mysql主从复制环境搭建
使用mysql，容灾是经常要考虑的事，主从复制是比较常见的一种容灾机制，类似oracle的OGG，只是还要简单很多
利用主从复制可以搭建很复杂的主从环境，比如主-从（1）-从，主-从（2）-从等等，这里只做一个简单的例子，即简单的主-从环境，旨在掌握好原理，即可
本环境的ip规划如下：主（192.168.1.130）、从（192.168.1.131）
不絮叨，我们直奔主题吧
一、主库上的操作
1、修改/etc/my.cnf 增加log-bin、server-id，我的my.cnf如下
[root@ct-test1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id=101
这里需要说的是server-id修改后，mysql的数据库就不同于原库了，所以不要乱改哈
2、创建复制用户repl
这个只需要在主库上执行，因为下文是将整个数据库导入备库，所以用户也是会同步过去的（如果不是全库备份，就需要在备库上执行此步骤）
这里有三步：
a、创建用户；
b、给予权限（我这里给了所有权限，按照官方文档中只给予GRANT REPLICATION SLAVE ON *.* TO user 的权限，但在实际同步的过程中，由于同步用户没有特殊权限，比如创建表、数据库等，就会报错）
c、flush PRIVILEGES，此步骤是确保权限可以及时跟新
mysql> CREATE USER repl IDENTIFIED BY 'Zxt1234!';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL ON *.* TO repl;
Query OK, 0 rows affected (0.01 sec)
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
3、查看master的Binary Log
这里需要记住这两个字段file和position，回头启动复制的时候会用到
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、备份主数据库
[root@ct-test1 ~]# mysqldump --password=Zxt1234! --all-databases --master-data > /mysql/dbdump.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@ct-test1 ~]# ls /mysql/dbdump.db
/mysql/dbdump.db
[root@ct-test1 ~]# ll /mysql/dbdump.db
-rw-r--r-- 1 root root 764914 Jun 24 17:59 /mysql/dbdump.db
ok，主库的操作就完成了，下来备库，这里备库采用恢复主库的备份文件来实现，所以会省去一些步骤，比如创建同步用户等
1、修改my.cnf，增加server-id
我备库的my.cnf如下：
[root@ct-test1 soft]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
2、完了后记得重启数据库
[root@ct-test1 soft]# systemctl restart mysqld
三、备库数据初始化
这里用主库的备份来回灌数据库，主库的备份见上边/mysql/dbdump.db，这里只需要在主库上执行如下语句即可：
[root@ct-test1 ~]# mysql --password=Zxt1234! -h 192.168.1.131 < /mysql/dbdump.db
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ct-test1 ~]#
四、同步主、备关系
在备库上执行如下命令：（这里mysql-bin.000002、154来源于主库SHOW MASTER STATUS的数据）
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.130', MASTER_USER='repl', MASTER_PASSWORD='Zxt1234!', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
五、开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
六、检查复制状态
这个必须要说一下，Slave_IO_Running、Slave_SQL_Running同时为yes才说明同步环境搭建ok
当然现实环境中（往往第一次搭建的时候会出很多问题），不会那么顺利，当不全为yes的时候：第一查看mysql.log日志，第二结合下边3个命令慢慢调：stop slave（停止复制）、reset slave（重置）、start slave
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: ct-test1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.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: 154
Relay_Log_Space: 530
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: 101
Master_UUID: 3f93c491-39ee-11e6-8726-000c29916128
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ok，搭建完成，很简单吧，下边做一下同步测试
我们在主库上执行建库、建表、插入数据等操作，然后从备库检查是否同步过去
I、主库上的操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database zxt;
Query OK, 1 row affected (0.04 sec)
mysql> use zxt;
Database changed
mysql> create tables tab_test(id1 varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables tab_test(id1 varchar(10))' at line 1
mysql> create table tab_test(id1 varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tab_test values ('12345');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
II.备库上检查结果如下：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zxt |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zxt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_zxt |
+---------------+
| tab_test |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tab_test;
+-------+
| id1 |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)
mysql>
利用主从复制可以搭建很复杂的主从环境，比如主-从（1）-从，主-从（2）-从等等，这里只做一个简单的例子，即简单的主-从环境，旨在掌握好原理，即可
本环境的ip规划如下：主（192.168.1.130）、从（192.168.1.131）
不絮叨，我们直奔主题吧
一、主库上的操作
1、修改/etc/my.cnf 增加log-bin、server-id，我的my.cnf如下
[root@ct-test1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin=mysql-bin
server-id=101
这里需要说的是server-id修改后，mysql的数据库就不同于原库了，所以不要乱改哈
2、创建复制用户repl
这个只需要在主库上执行，因为下文是将整个数据库导入备库，所以用户也是会同步过去的（如果不是全库备份，就需要在备库上执行此步骤）
这里有三步：
a、创建用户；
b、给予权限（我这里给了所有权限，按照官方文档中只给予GRANT REPLICATION SLAVE ON *.* TO user 的权限，但在实际同步的过程中，由于同步用户没有特殊权限，比如创建表、数据库等，就会报错）
c、flush PRIVILEGES，此步骤是确保权限可以及时跟新
mysql> CREATE USER repl IDENTIFIED BY 'Zxt1234!';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT ALL ON *.* TO repl;
Query OK, 0 rows affected (0.01 sec)
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
3、查看master的Binary Log
这里需要记住这两个字段file和position，回头启动复制的时候会用到
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、备份主数据库
[root@ct-test1 ~]# mysqldump --password=Zxt1234! --all-databases --master-data > /mysql/dbdump.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@ct-test1 ~]# ls /mysql/dbdump.db
/mysql/dbdump.db
[root@ct-test1 ~]# ll /mysql/dbdump.db
-rw-r--r-- 1 root root 764914 Jun 24 17:59 /mysql/dbdump.db
ok，主库的操作就完成了，下来备库，这里备库采用恢复主库的备份文件来实现，所以会省去一些步骤，比如创建同步用户等
1、修改my.cnf，增加server-id
我备库的my.cnf如下：
[root@ct-test1 soft]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
2、完了后记得重启数据库
[root@ct-test1 soft]# systemctl restart mysqld
三、备库数据初始化
这里用主库的备份来回灌数据库，主库的备份见上边/mysql/dbdump.db，这里只需要在主库上执行如下语句即可：
[root@ct-test1 ~]# mysql --password=Zxt1234! -h 192.168.1.131 < /mysql/dbdump.db
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ct-test1 ~]#
四、同步主、备关系
在备库上执行如下命令：（这里mysql-bin.000002、154来源于主库SHOW MASTER STATUS的数据）
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.130', MASTER_USER='repl', MASTER_PASSWORD='Zxt1234!', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
五、开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
六、检查复制状态
这个必须要说一下，Slave_IO_Running、Slave_SQL_Running同时为yes才说明同步环境搭建ok
当然现实环境中（往往第一次搭建的时候会出很多问题），不会那么顺利，当不全为yes的时候：第一查看mysql.log日志，第二结合下边3个命令慢慢调：stop slave（停止复制）、reset slave（重置）、start slave
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: ct-test1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.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: 154
Relay_Log_Space: 530
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: 101
Master_UUID: 3f93c491-39ee-11e6-8726-000c29916128
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
我们在主库上执行建库、建表、插入数据等操作，然后从备库检查是否同步过去
I、主库上的操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database zxt;
Query OK, 1 row affected (0.04 sec)
mysql> use zxt;
Database changed
mysql> create tables tab_test(id1 varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tables tab_test(id1 varchar(10))' at line 1
mysql> create table tab_test(id1 varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tab_test values ('12345');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
II.备库上检查结果如下：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zxt |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zxt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_zxt |
+---------------+
| tab_test |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tab_test;
+-------+
| id1 |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)
mysql>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/694276/viewspace-2120943/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
mysql组件之memcache
请登录后发表评论
登录
全部评论
