Mysql主从配置(主从介绍、准备工作、配置主、配置从、测试主从同步)
时间:2019-01-14 22:40 来源:未知 作者:IT
主从介绍
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：
在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。
Mysql主从复制的实现原理图大致如下(来源网络)：
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。 实现步骤：
主将更改操作记录到binlog里
从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
从根据relaylog里面的sql语句按顺序执行
准备工作
1.主从数据库版本最好一致
2.主从数据库内数据保持一致
主数据库(yolks2)：192.168.248.129
从数据库(yolks1)：192.168.248.128
配置主
1.修改yolks2机器上的mysql配置文件，添加以下代码：
server-id=129 #此处暂定ip末端数字
log_bin=yolks2 #bin_log前缀名称，暂定和机器hostname一致
如下图所示：
2.重启mysql服务
/etc/init.d/mysqld restart
3.查看生成的文件:实现主从的根本
[root@yolks2 ~]# ls -lt /data/mysql/ |grep yolks2*
-rw-rw---- 1 mysql mysql 48988 9月 2 16:38 yolks2.err
-rw-rw---- 1 mysql mysql 5 9月 2 16:38 yolks2.pid
-rw-rw---- 1 mysql mysql 120 9月 2 16:38 yolks2.000001
-rw-rw---- 1 mysql mysql 16 9月 2 16:38 yolks2.index
4.备份zrlog数据库 : zrlog之前搭建测试博客测试数据库
mysqldump -uroot -p'mysql123' zrlog > /tmp/zrlog.sql
5.创建新数据库:test_copy_AB
mysql -uroot -p'mysql123' -e "create database test_copy_AB"
6.将步骤4备份的zrlog数据恢复到步骤5新创建的test_copy_AB数据库中
mysql -uroot -p'mysql123' test_copy_AB < /tmp/zrlog.sql
7.通过再次重复步骤3进行比较导入新库后，bin_log的文件大小有变化
导入后
[root@yolks2 ~]# !ls
ls -lt /data/mysql/ |grep yolks2*
-rw-rw---- 1 mysql mysql 11417 9月 2 16:50 yolks2.000001
导入前
[root@yolks2 ~]# ls -lt /data/mysql/ |grep yolks2*
-rw-rw---- 1 mysql mysql 120 9月 2 16:38 yolks2.000001
说明数据库已经将信息记录到yolks2.000001文件中去
8.创建用作同步数据的用户
grant replication slave on *.* to 'repl'@192.168.248.128 identified by 'repl123456';#此处ip指的是从机器地址
9.然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作
flush tables with read lock;
10.显示File和Position信息，从机器设置会需要
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| yolks2.000001 | 11629 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
11.将本地需要同步的数据库先做备份
mysqldump -uroot -p'mysql123' test > /tmp/test.sql #备份数据库到文件命令
配置从
1.修改数据库配置文件，然后添加从专属的server-id
server-id=128
如下图所示：
2.将主机器上yolks2的/tmp/目录下备份的sql文件同步过来
scp 192.168.248.129:/tmp/*.sql /tmp/
3.从机器上做别名
alias 'mysql=/usr/local/mysql/bin/mysql' #做mysql别名
alias 'mysqldump=/usr/local/mysql/bin/mysqldump' #做mysqldump别名
4.创建和主机器上yolks2对应的几个数据库
create database dbtest;
create database zrlog;
create database mysql2;
create database test_copy_AB;
5.然后将从主机器yolks2上同步过来的备份sql文件导入
mysql -uroot mysql2 < /tmp/mysql2.sql
mysql -uroot dbtest < /tmp/dbtest.sql
mysql -uroot zrlog < /tmp/zrlog.sql
mysql -uroot test_copy_AB < /tmp/test_copy_AB.sql
6.从机器进入mysql关闭同步
stop slave;
7.更改配置同步主机器的设置
选项：
master_log_file ： 主机器执行show master status命令结果的File即可
master_log_pos ： 主机器执行show master status命令结果的Position即可
效果如下：
mysql> change master to master_host='192.168.248.129',master_user='repl', master_password='repl123456', master_log_file='yolks2.000001', master_log_pos=11629；
Query OK, 0 rows affected, 2 warnings (0.01 sec)
8.开启同步
start slave;
9.查看是否可以同步的输出显示
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.248.129
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: yolks2.000001
Read_Master_Log_Pos: 11629
Relay_Log_File: yolks-001-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: yolks2.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 11629
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repl@192.168.248.129:3306' - retry-time: 60 retries: 2
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 180902 18:25:48
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
Slave_IO_Running: Connecting
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxxx' (113)
linux搭建mysql主从数据库,Last_IO_Errno: 2003
找到原因：需要主机器手动停止防火墙
systemctl disable firewalld
service firewalld stop
从机器重新尝试显示状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.248.129
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: aminglinux1.000002
Read_Master_Log_Pos: 120
Relay_Log_File: yolks-001-relay-bin.000003
Relay_Log_Pos: 285
Relay_Master_Log_File: aminglinux1.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 627
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 129
Master_UUID: deba07ec-aebd-11e8-90fc-000c2973b522
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
判断成功还是失败的关键参数：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
及一些error是否有数值不为0的情况
10.主机器yolks2上执行读写解除
unlock tables
测试主从同步
my.cnf其他几个忽略掉的配置参数
主机器：
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库
从机器：
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table= //如aming.%, 支持通配符%
replicate_wild_ignore_table=
1.主机器登录mysql使用test_copy_AB数据库
mysql> show tables;
+------------------------+
| Tables_in_test_copy_AB |
+------------------------+
| comment |
| link |
| log |
| lognav |
| plugin |
| tag |
| type |
| user |
| website |
+------------------------+
9 rows in set (0.00 sec)
2.将tag表清空
truncate table tag;
3.将数据增删改查查看从机变化进行测试
(责任编辑：IT)
| 主从介绍 MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。 下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)： 在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。 Mysql主从复制的实现原理图大致如下(来源网络)： MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。 MySQL主从是基于binlog的，主上须开启binlog才能进行主从。 实现步骤： 主将更改操作记录到binlog里 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里 从根据relaylog里面的sql语句按顺序执行 准备工作 1.主从数据库版本最好一致 2.主从数据库内数据保持一致 主数据库(yolks2)：192.168.248.129 从数据库(yolks1)：192.168.248.128 配置主 1.修改yolks2机器上的mysql配置文件，添加以下代码： server-id=129 #此处暂定ip末端数字 log_bin=yolks2 #bin_log前缀名称，暂定和机器hostname一致 如下图所示： 2.重启mysql服务 /etc/init.d/mysqld restart 3.查看生成的文件:实现主从的根本 [root@yolks2 ~]# ls -lt /data/mysql/ |grep yolks2* -rw-rw---- 1 mysql mysql 48988 9月 2 16:38 yolks2.err -rw-rw---- 1 mysql mysql 5 9月 2 16:38 yolks2.pid -rw-rw---- 1 mysql mysql 120 9月 2 16:38 yolks2.000001 -rw-rw---- 1 mysql mysql 16 9月 2 16:38 yolks2.index 4.备份zrlog数据库 : zrlog之前搭建测试博客测试数据库 mysqldump -uroot -p'mysql123' zrlog > /tmp/zrlog.sql 5.创建新数据库:test_copy_AB mysql -uroot -p'mysql123' -e "create database test_copy_AB" 6.将步骤4备份的zrlog数据恢复到步骤5新创建的test_copy_AB数据库中 mysql -uroot -p'mysql123' test_copy_AB < /tmp/zrlog.sql 7.通过再次重复步骤3进行比较导入新库后，bin_log的文件大小有变化 导入后 [root@yolks2 ~]# !ls ls -lt /data/mysql/ |grep yolks2* -rw-rw---- 1 mysql mysql 11417 9月 2 16:50 yolks2.000001 导入前 [root@yolks2 ~]# ls -lt /data/mysql/ |grep yolks2* -rw-rw---- 1 mysql mysql 120 9月 2 16:38 yolks2.000001 说明数据库已经将信息记录到yolks2.000001文件中去 8.创建用作同步数据的用户 grant replication slave on *.* to 'repl'@192.168.248.128 identified by 'repl123456';#此处ip指的是从机器地址 9.然后刷新所有的表，同时给数据库加上一把锁，阻止对数据库进行任何的写操作 flush tables with read lock; 10.显示File和Position信息，从机器设置会需要 mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | yolks2.000001 | 11629 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 11.将本地需要同步的数据库先做备份 mysqldump -uroot -p'mysql123' test > /tmp/test.sql #备份数据库到文件命令 配置从 1.修改数据库配置文件，然后添加从专属的server-id server-id=128 如下图所示： 2.将主机器上yolks2的/tmp/目录下备份的sql文件同步过来 scp 192.168.248.129:/tmp/*.sql /tmp/ 3.从机器上做别名 alias 'mysql=/usr/local/mysql/bin/mysql' #做mysql别名 alias 'mysqldump=/usr/local/mysql/bin/mysqldump' #做mysqldump别名 4.创建和主机器上yolks2对应的几个数据库 create database dbtest; create database zrlog; create database mysql2; create database test_copy_AB; 5.然后将从主机器yolks2上同步过来的备份sql文件导入 mysql -uroot mysql2 < /tmp/mysql2.sql mysql -uroot dbtest < /tmp/dbtest.sql mysql -uroot zrlog < /tmp/zrlog.sql mysql -uroot test_copy_AB < /tmp/test_copy_AB.sql 6.从机器进入mysql关闭同步 stop slave; 7.更改配置同步主机器的设置 选项： master_log_file ： 主机器执行show master status命令结果的File即可 master_log_pos ： 主机器执行show master status命令结果的Position即可 效果如下： mysql> change master to master_host='192.168.248.129',master_user='repl', master_password='repl123456', master_log_file='yolks2.000001', master_log_pos=11629； Query OK, 0 rows affected, 2 warnings (0.01 sec) 8.开启同步 start slave; 9.查看是否可以同步的输出显示 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.248.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: yolks2.000001 Read_Master_Log_Pos: 11629 Relay_Log_File: yolks-001-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: yolks2.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 11629 Relay_Log_Space: 120 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'repl@192.168.248.129:3306' - retry-time: 60 retries: 2 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 180902 18:25:48 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) Slave_IO_Running: Connecting ERROR 2003 (HY000): Can't connect to MySQL server on 'xxxx' (113) linux搭建mysql主从数据库,Last_IO_Errno: 2003 找到原因：需要主机器手动停止防火墙 systemctl disable firewalld service firewalld stop 从机器重新尝试显示状态 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.248.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: aminglinux1.000002 Read_Master_Log_Pos: 120 Relay_Log_File: yolks-001-relay-bin.000003 Relay_Log_Pos: 285 Relay_Master_Log_File: aminglinux1.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 627 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 129 Master_UUID: deba07ec-aebd-11e8-90fc-000c2973b522 Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) 判断成功还是失败的关键参数： Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 及一些error是否有数值不为0的情况 10.主机器yolks2上执行读写解除 unlock tables 测试主从同步 my.cnf其他几个忽略掉的配置参数 主机器： binlog-do-db= //仅同步指定的库 binlog-ignore-db= //忽略指定库 从机器： replicate_do_db= replicate_ignore_db= replicate_do_table= replicate_ignore_table= replicate_wild_do_table= //如aming.%, 支持通配符% replicate_wild_ignore_table= 1.主机器登录mysql使用test_copy_AB数据库 mysql> show tables; +------------------------+ | Tables_in_test_copy_AB | +------------------------+ | comment | | link | | log | | lognav | | plugin | | tag | | type | | user | | website | +------------------------+ 9 rows in set (0.00 sec) 2.将tag表清空 truncate table tag; 3.将数据增删改查查看从机变化进行测试 (责任编辑：IT) |