配置Mysql數(shù)據(jù)庫的主從同步教程

1、配置Mysql數(shù)據(jù)庫的主從同步教程主從同步是實(shí)現(xiàn)網(wǎng)站分布式數(shù)據(jù)處理一個非常常用的方案了,今天我來為各位介紹配置Mysql數(shù)據(jù)庫的主從同步(雙主)教程,希望下文能幫助到各位哦.配置Mysql數(shù)據(jù)庫的主從同步(一主一從).一、主庫開啟BINLOG、server-idrootMaster-Mysql # grep -E "server-id|log-bin" /etc/f log-bin = /usr/local/mysql/data/mysql-bin server-id = 1 mysql> show variables like '%log_bin%'

2、 +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +-+-+ 6 rows in set (0.01 sec) -

3、 mysql> show variables like '%server_id%' +-+-+ | Variable_name | Value | +-+-+ | server_id | 1 | | server_id_bits | 32 | +-+-+ 2 rows in set (0.00 sec) 備注:以上兩個信息必須在mysqld模塊下！二、給從庫授權(quán)mysql> grant replication slave on *.* to byrd'192.168.199.%' identified by 'admin' mysql

4、> flush privileges; mysql> select user,host from mysql.user; +-+-+ | user | host | +-+-+ | root | | | byrd | 192.168.199.% | | root | :1 | | root | lamp | | root | localhost | +-+-+ 5 rows in set (0.00 sec) 鎖表前建立點(diǎn)數(shù)據(jù):mysql> create database hitest; mysql> show databases; +-+ | Da

5、tabase | +-+ | hitest | +-+ 6 rows in set (0.00 sec) mysql> use hitest; mysql> create table test( -> id int(4) not null primary key auto_increment, -> name char(20) not null -> ); Query OK, 0 rows affected (1.80 sec) mysql> show tables ; +-+ | Tables_in_hitest | +-+ | test | +-+ my

6、sql> insert into test(id,name) values(1,'zy'); mysql> select * from test; +-+-+ | id | name | +-+-+ | 1 | zy | +-+-+ 三、鎖表、備份、解鎖mysql> flush table with read lock; #鎖表 mysql> show variables like '%timeout%' #鎖表時間 +-+-+ | Variable_name | Value | +-+-+ | interactive_timeout |

7、 28800 | | wait_timeout | 28800 | +-+-+ 12 rows in set (0.06 sec) mysql> show master status; #binlog日志位置 +-+-+-+-+-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-+-+-+-+-+ | mysql-bin.000004 | 1305 | | | | +-+-+-+-+-+ 1 row in set (0.03 sec) rootMaster-Mysql # /usr/

8、local/mysql/bin/mysqldump -uroot -p'' -B -A |gzip >/tmp/all.sql.gz #新窗口備份 Enter password: mysql> unlock table; #解鎖 #解鎖后主庫操作如下：# mysql> use hitest mysql> insert into test(id,name) values(2,'binghe'); mysql> select * from test; +-+-+ | id | name | +-+-+ | 1 | zy | | 2 |

9、binghe | +-+-+ mysql> create database hxy; #解鎖后主庫操作完成# 備注:備份數(shù)據(jù)需要重新打開新窗口,不然鎖表就自動失效.四、主庫導(dǎo)入到從庫#主庫操作# rootMaster-Mysql tmp# ll -rw-r-r-. 1 root root 162236 Jul 8 21:30 all.sql.gz rootMaster-Mysql tmp# gzip -d all.sql.gz rootMaster-Mysql tmp# ll -rw-r-r-. 1 root root 590351 Jul 8 21:30 all.sql #主庫完成#

10、#備注：將主庫導(dǎo)出的all.sql通過scp、ssh、sftp等方式拷貝到從庫服務(wù)器，此處略# rootSlave-Mysql # grep log-bin /etc/f #log-bin = /usr/local/mysql/data/mysql-bin rootSlave-Mysql # grep server-id /etc/f server-id = 2 rootSlave-Mysql # /etc/init.d/mysqld restart rootSlave-Mysql tmp# /usr/local/mysql/bin/mysql -uroot -p'admin'

11、 </tmp/all.sql Warning: Using a password on the command line interface can be insecure. rootSlave-Mysql tmp# /usr/local/mysql/bin/mysql -uroot -p'admin' mysql> use hitest; mysql> select * from test; +-+-+ | id | name | +-+-+ | 1 | zy | +-+-+ 1 row in set (0.00 sec) 六、從庫配置信息mysql>

12、 CHANGE MASTER TO -> MASTER_HOST='77', -> MASTER_PORT=3306, -> MASTER_USER='byrd', -> MASTER_PASSWORD='admin', -> MASTER_LOG_FILE='mysql-bin.000004', -> MASTER_LOG_POS=1305; Query OK, 0 rows affected, 2 warnings (1.96 sec) rootSlave-Mysq

13、l # ll /usr/local/mysql/data/ #備注：記錄MASTER的相關(guān)信息！ 七、啟動從庫同步mysql> start slave; mysql> show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 八、結(jié)果測試mysql> use hitest; mysql> select * from test; +-+-+ | id | name | +-+-+ | 1 | zy | | 2

14、| binghe | +-+-+ 2 rows in set (0.00 sec) rootMaster-Mysql # /usr/local/mysql/bin/mysql -uroot -p'' -e "create database zhihu;" #主庫建立了一個zhihu的數(shù)據(jù)庫 Enter password: rootSlave-Mysql # /usr/local/mysql/bin/mysql -uroot -p'' -e "show databases like 'zhihu'" Ente

15、r password: +-+ | Database (zhihu) | +-+ | zhihu | +-+ 配置Mysql數(shù)據(jù)庫的主從同步(雙主)已經(jīng)配置好的:主庫:77從庫:78rootMaster-Mysql # egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors" /etc/f log-bin = /usr/local/mysql/data/mysql-bin #必須 server-id = 1 #必須 log-slave-upda

16、tes #必須 auto_increment_increment = 2 #必須 auto_increment_offset = 1 #必須 slave-skip-errors = 1032,1062,1007 #非必須，建議 #主庫、從庫分隔符# rootSlave-Mysql data# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only" /etc/f #log-bin = /usr/local/mysql/data/mysql-bin server-id = 2

17、log-slave-updates log-bin = /usr/local/mysql/data/mysql-bin #read-only #雙主，此選項(xiàng)要注釋掉 slave-skip-errors = 1032,1062,1007 auto_increment_increment = 2 #ID自增間隔 auto_increment_offset = 2 #ID初始位置 78: mysql> stop slave; mysql> flush table with read lock; mysql> show master status; +-+-

18、+-+-+-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-+-+-+-+-+ | mysql-bin.000004 | 120 | | | | +-+-+-+-+-+ mysql> system /usr/local/sql/bin/mysqldump -uroot -p'' -A -B >/tmp/78.sql #如果主、從一致非必須 mysql> unlock tables; #同上 mysql> system ls

19、 -l /tmp/ -rw-r-r-. 1 root root 2887406 Jul 12 22:24 78.sql mysql> start slave; 77: rootMaster-Mysql # /usr/local/mysql/bin/mysql -uroot -p'' < /tmp/78.sql #如果主、從一致非必須 mysql> update mysql.user set password=PASSWORD('admin') where user=&

20、#39;root' rootMaster-Mysql # cat |/usr/local/mysql/bin/mysql -uroot -p'admin' <<EOF #必須 > CHANGE MASTER TO > MASTER_HOST='78', > MASTER_PORT=3306, > MASTER_USER='byrd', > MASTER_PASSWORD='admin', > MASTER_LOG_FILE='mysql-bi

21、n.000004', > MASTER_LOG_POS=120; > EOF mysql> start slave; mysql> show slave statusG * 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 78 Master_User: byrd Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 9

22、38 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 1101 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Skip_Counter: 0 Exec_Master_Log_Pos: 938 Relay_Log_Space: 1275 Until_Condition: None Until_Log_Pos: 0 Master_SSL_Allowed: No Seconds_Behin

23、d_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_SQL_Errno: 0 Master_Server_Id: 2 Master_UUID: 34d672c3-d292-11e3-9ff5-00155dc7834c Master_Info_File: /usr/local/mysql/data/ SQL_Delay: 0 - SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; w

24、aiting for the slave I/O thread to update it Master_Retry_Count: 86400 測試:77:mysql> use hitest; mysql> CREATE TABLE ces ( -> REL_ID bigint(12) NOT NULL auto_increment COMMENT 'id', -> TITLE varchar(255) NOT NULL COMMENT 'biaoti', -> PRIMARY KEY (REL_ID) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; mysql> insert into ces(TITLE) values('test'); mysql> insert into ces(TI