当前位置：首页 > Mysql教程 > 列表

配置Mysql数据库的主从同步教程

发布:smiling　来源: PHP粉丝网 　添加日期：2015-04-18 10:11:19　浏览: 评论：0　

主从同步是实现网站分布式数据处理一个非常常用的方案了,今天我来为各位介绍配置Mysql数据库的主从同步(双主)教程,希望下文能帮助到各位哦.

配置Mysql数据库的主从同步(一主一从).

一、主库开启BINLOG、server-id

  1. [root@Master-Mysql ~]# grep -E "server-id|log-bin" /etc/my.cnf 
  2. log-bin = /usr/local/mysql/data/mysql-bin 
  3. server-id = 1 
  4. mysql> show variables like '%log_bin%'; 
  5. +---------------------------------+---------------------------------------+ 
  6. | Variable_name                   | Value                                 | 
  7. +---------------------------------+---------------------------------------+ 
  8. | log_bin                         | ON                                    | 
  9. | log_bin_basename                | /usr/local/mysql/data/mysql-bin       | 
  10. | log_bin_index                   | /usr/local/mysql/data/mysql-bin.index | 
  11. | log_bin_trust_function_creators | OFF                                   | 
  12. | log_bin_use_v1_row_events       | OFF                                   | 
  13. | sql_log_bin                     | ON                                    | 
  14. +---------------------------------+---------------------------------------+ 
  15. 6 rows in set (0.01 sec)  --phpfensi.com 
  16. mysql> show variables like '%server_id%'; 
  17. +----------------+-------+ 
  18. | Variable_name  | Value | 
  19. +----------------+-------+ 
  20. | server_id      | 1     | 
  21. | server_id_bits | 32    | 
  22. +----------------+-------+ 
  23. 2 rows in set (0.00 sec) 

备注:以上两个信息必须在[mysqld]模块下！！！

二、给从库授权

  1. mysql> grant replication slave on *.* to byrd@'192.168.199.%' identified by 'admin'; 
  2. mysql> flush privileges; 
  3. mysql> select user,host from mysql.user; 
  4. +------+---------------+ 
  5. | user | host          | 
  6. +------+---------------+ 
  7. | root | 127.0.0.1     | 
  8. | byrd | 192.168.199.% | 
  9. | root | ::1           | 
  10. | root | lamp          | 
  11. | root | localhost     | 
  12. +------+---------------+ 
  13. 5 rows in set (0.00 sec) 

锁表前建立点数据:

  1. mysql> create database hitest; 
  2. mysql> show databases; 
  3. +--------------------+ 
  4. | Database           | 
  5. +--------------------+ 
  6. | hitest             | 
  7. +--------------------+ 
  8. 6 rows in set (0.00 sec) 
  9. mysql> use hitest; 
  10. mysql> create table test(  
  11.     -> id int(4) not null primary key auto_increment, 
  12.     -> name char(20) not null 
  13.     -> ); 
  14. Query OK, 0 rows affected (1.80 sec) 
  15. mysql> show tables ; 
  16. +------------------+ 
  17. | Tables_in_hitest | 
  18. +------------------+ 
  19. | test             | 
  20. +------------------+ 
  21. mysql> insert into test(id,name) values(1,'zy'); 
  22. mysql> select * from test; 
  23. +----+------+ 
  24. | id | name | 
  25. +----+------+ 
  26. |  1 | zy   | 
  27. +----+------+ 

三、锁表、备份、解锁

  1. mysql> flush table with read lock;    #锁表 
  2. mysql> show variables like '%timeout%';    #锁表时间 
  3. +-----------------------------+----------+ 
  4. | Variable_name               | Value    | 
  5. +-----------------------------+----------+ 
  6. | interactive_timeout         | 28800    | 
  7. | wait_timeout                | 28800    | 
  8. +-----------------------------+----------+ 
  9. 12 rows in set (0.06 sec) 
  10. mysql> show master status;    #binlog日志位置 
  11. +------------------+----------+--------------+------------------+-------------------+ 
  12. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
  13. +------------------+----------+--------------+------------------+-------------------+ 
  14. | mysql-bin.000004 |     1305 |              |                  |                   | 
  15. +------------------+----------+--------------+------------------+-------------------+ 
  16. 1 row in set (0.03 sec) 
  17. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'' -B -A |gzip >/tmp/all.sql.gz    #新窗口备份 
  18. Enter password:  
  19. mysql> unlock table;    #解锁 
  20. ###############解锁后主库操作如下：############### 
  21. mysql> use hitest 
  22. mysql> insert into test(id,name) values(2,'binghe'); 
  23. mysql> select * from test; 
  24. +----+--------+ 
  25. | id | name   | 
  26. +----+--------+ 
  27. |  1 | zy     | 
  28. |  2 | binghe | 
  29. +----+--------+ 
  30. mysql> create database hxy; 
  31. ###############解锁后主库操作完成~############### 

备注:备份数据需要重新打开新窗口,不然锁表就自动失效.

四、主库导入到从库

  1. ################主库操作################ 
  2. [root@Master-Mysql tmp]# ll 
  3. -rw-r--r--. 1 root  root  162236 Jul  8 21:30 all.sql.gz 
  4. [root@Master-Mysql tmp]# gzip -d all.sql.gz  
  5. [root@Master-Mysql tmp]# ll 
  6. -rw-r--r--. 1 root  root  590351 Jul  8 21:30 all.sql 
  7. ################主库完成################ 
  8. ##备注：将主库导出的all.sql通过scp、ssh、sftp等方式拷贝到从库服务器，此处略## 
  9. [root@Slave-Mysql ~]# grep log-bin /etc/my.cnf 
  10. #log-bin = /usr/local/mysql/data/mysql-bin 
  11. [root@Slave-Mysql ~]# grep server-id /etc/my.cnf 
  12. server-id = 2 
  13. [root@Slave-Mysql ~]# /etc/init.d/mysqld restart 
  14. [root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin' </tmp/all.sql  
  15. Warning: Using a password on the command line interface can be insecure. 
  16. [root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -uroot -p'admin'  
  17. mysql> use hitest; 
  18. mysql> select * from test; 
  19. +----+------+ 
  20. | id | name | 
  21. +----+------+ 
  22. |  1 | zy   | 
  23. +----+------+ 
  24. 1 row in set (0.00 sec) 

六、从库配置信息

  1. mysql> CHANGE MASTER TO 
  2.     -> MASTER_HOST='192.168.199.177', 
  3.     -> MASTER_PORT=3306, 
  4.     -> MASTER_USER='byrd', 
  5.     -> MASTER_PASSWORD='admin', 
  6.     -> MASTER_LOG_FILE='mysql-bin.000004', 
  7.     -> MASTER_LOG_POS=1305; 
  8. Query OK, 0 rows affected, 2 warnings (1.96 sec) 
  9. [root@Slave-Mysql ~]# ll /usr/local/mysql/data/master.info  
  10. ##备注：master.info记录MASTER的相关信息！ 

七、启动从库同步

  1. mysql> start slave; 
  2. mysql> show slave status＼G 
  3.             Slave_IO_Running: Yes 
  4.             Slave_SQL_Running: Yes 
  5.             Seconds_Behind_Master: 0 

八、结果测试

  1. mysql> use hitest; 
  2. mysql> select * from test; 
  3. +----+--------+ 
  4. | id | name   | 
  5. +----+--------+ 
  6. |  1 | zy     | 
  7. |  2 | binghe | 
  8. +----+--------+ 
  9. 2 rows in set (0.00 sec) 
  10. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "create database zhihu;"    #主库建立了一个zhihu的数据库 
  11. Enter password:  
  12. [root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e "show databases like 'zhihu'"; 
  13. Enter password:  
  14. +------------------+ 
  15. | Database (zhihu) | 
  16. +------------------+ 
  17. | zhihu            | 
  18. +------------------+ 

配置Mysql数据库的主从同步(双主)

已经配置好的:

主库:192.168.199.177

从库:192.168.199.178

  1. [root@Master-Mysql ~]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors" /etc/my.cnf 
  2. log-bin = /usr/local/mysql/data/mysql-bin    #必须 
  3. server-id = 1    #必须 
  4. log-slave-updates    #必须 
  5. auto_increment_increment = 2    #必须 
  6. auto_increment_offset = 1    #必须 
  7. slave-skip-errors = 1032,1062,1007    #非必须，建议 
  8. ########################主库、从库分隔符######################## 
  9. [root@Slave-Mysql data]# egrep "server-id|log-slave|log-bin|auto_increment|slave-skip-errors|read-only" /etc/my.cnf 
  10. #log-bin = /usr/local/mysql/data/mysql-bin 
  11. server-id = 2 
  12. log-slave-updates 
  13. log-bin = /usr/local/mysql/data/mysql-bin 
  14. #read-only    #双主，此选项要注释掉 
  15. slave-skip-errors = 1032,1062,1007 
  16. auto_increment_increment = 2    #ID自增间隔 
  17. auto_increment_offset = 2    #ID初始位置 
  18. 192.168.199.178: 
  19. mysql> stop slave; 
  20. mysql> flush table with read lock;  
  21. mysql> show master status; 
  22. +------------------+----------+--------------+------------------+-------------------+ 
  23. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
  24. +------------------+----------+--------------+------------------+-------------------+ 
  25. | mysql-bin.000004 |      120 |              |                  |                   | 
  26. +------------------+----------+--------------+------------------+-------------------+ 
  27. mysql> system /usr/local/mysql/bin/mysqldump -uroot -p'' -A -B >/tmp/192.168.199.178.sql   #如果主、从一致非必须 
  28. mysql> unlock tables;    #同上 
  29. mysql> system ls -l /tmp/ 
  30. -rw-r--r--.  1 root  root   2887406 Jul 12 22:24 192.168.199.178.sql 
  31. mysql> start slave; 
  32. 192.168.199.177: 
  33. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' < /tmp/192.168.199.178.sql    #如果主、从一致非必须 
  34. mysql> update mysql.user set password=PASSWORD('admin') where user='root'; 
  35. [root@Master-Mysql ~]# cat |/usr/local/mysql/bin/mysql -uroot -p'admin' <<EOF    #必须 
  36. > CHANGE MASTER TO 
  37. > MASTER_HOST='192.168.199.178', 
  38. > MASTER_PORT=3306, 
  39. > MASTER_USER='byrd', 
  40. > MASTER_PASSWORD='admin', 
  41. > MASTER_LOG_FILE='mysql-bin.000004', 
  42. > MASTER_LOG_POS=120; 
  43. > EOF 
  44. mysql> start slave; 
  45. mysql> show slave status＼G 
  46. *************************** 1. row *************************** 
  47.                Slave_IO_State: Waiting for master to send event 
  48.                   Master_Host: 192.168.199.178 
  49.                   Master_User: byrd 
  50.                   Master_Port: 3306 
  51.                 Connect_Retry: 60 
  52.               Master_Log_File: mysql-bin.000004 
  53.           Read_Master_Log_Pos: 938 
  54.                Relay_Log_File: mysqld-relay-bin.000002 
  55.                 Relay_Log_Pos: 1101 
  56.         Relay_Master_Log_File: mysql-bin.000004 
  57.              Slave_IO_Running: Yes 
  58.             Slave_SQL_Running: Yes 
  59.                    Last_Errno: 0 
  60.                  Skip_Counter: 0 
  61.           Exec_Master_Log_Pos: 938 
  62.               Relay_Log_Space: 1275 
  63.               Until_Condition: None 
  64.                 Until_Log_Pos: 0 
  65.            Master_SSL_Allowed: No 
  66.         Seconds_Behind_Master: 0 
  67. Master_SSL_Verify_Server_Cert: No 
  68.                 Last_IO_Errno: 0 
  69.                Last_SQL_Errno: 0 
  70.              Master_Server_Id: 2 
  71.                   Master_UUID: 34d672c3-d292-11e3-9ff5-00155dc7834c 
  72.              Master_Info_File: /usr/local/mysql/data/master.info 
  73.                     SQL_Delay: 0  --phpfensi.com 
  74.           SQL_Remaining_Delay: NULL 
  75.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 
  76.            Master_Retry_Count: 86400 

测试:192.168.199.177:

  1. mysql> use hitest; 
  2. mysql> CREATE TABLE `ces` ( 
  3.     -> `REL_ID` bigint(12) NOT NULL auto_increment COMMENT 'id', 
  4.     -> `TITLE` varchar(255) NOT NULL COMMENT 'biaoti', 
  5.     -> PRIMARY KEY (`REL_ID`) 
  6.     -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
  7. mysql> insert into ces(TITLE) values('test'); 
  8. mysql> insert into ces(TITLE) values('test'); 
  9. mysql> insert into ces(TITLE) values('test'); 
  10. mysql> insert into ces(TITLE) values('test25'); 
  11. mysql> select * from ces; 
  12. +--------+-------+ 
  13. | REL_ID | TITLE | 
  14. +--------+-------+ 
  15. |      1 | test  | 
  16. |      3 | test  | 
  17. |      5 | test  | 
  18. |     25 | test25| 
  19. +--------+--------+ 
  20. 3 rows in set (0.03 sec) 
  21. 192.168.199.178: 
  22. mysql> use hitest; 
  23. mysql> insert into ces(TITLE) values('test26'); 
  24. mysql> insert into ces(TITLE) values('test28'); 
  25. mysql> insert into ces(TITLE) values('test30'); 
  26. mysql> select * from ces; 
  27. +--------+--------+ 
  28. | REL_ID | TITLE  | 
  29. +--------+--------+ 
  30. |      1 | test   | 
  31. |      3 | test   | 
  32. |      5 | test   | 
  33. |     26 | test26 | 
  34. |     28 | test28 | 
  35. |     30 | test30 | 
  36. +--------+--------+ 
  37. 17 rows in set (0.00 sec) 

说明:如果一主、一丛已经做好,只要知道从库位置点(show master status;)、然后之前主库执行(CHANGE MASTER)、之前主库开启slave(start slave)即可,其中数据库备份等步骤可以省略,如果主从有一些数据库不一致则同上操作.

Tags: 配置Mysql Mysql主从同步

分享到：

推荐文章

热门文章

最新评论文章