【MySQL 5.7.19】针对在用生产库做主从复制
xixuefeng
MySQL, MySQL-Install
2017-09-12 18:12:57
1,318 次浏览
【MySQL 5.7.19】针对在用生产库做主从复制已关闭评论
针对在用生产库做主从复制
1:从库清理环境
1 2 3 4 5 6 7 |
[root@mss mysql]# pwd /data/db/mysql [root@mss mysql]# [root@mss mysql]# ls 2587 [root@mss mysql]# [root@mss mysql]# mv 2587/ 2587.bak |
2:主库清除日志(不是必须)
1 2 3 4 5 |
## 如果生产库中有大量的日志,那么会占用大量的空间,不便于我们以拷贝的方式克隆数据库 mysql> RESET MASTER ; Query OK, 0 rows affected (0.02 sec) mysql> |
3:主库停库
1 2 3 |
[root@msp 2587]# service mysql stop Shutting down MySQL.. SUCCESS! [root@msp 2587]# |
4:主库打包备份
1 2 3 4 5 6 |
[root@msp mysql]# pwd /data/db/mysql [root@msp mysql]# ls 2587 2587.tar.gz [root@msp mysql]# [root@msp mysql]# tar -zcvf 2587.tar.gz 2587/ |
5:将主库tar包scp到从库
1 2 3 |
[root@msp mysql]# ls 2587 2587.tar.gz [root@msp mysql]# scp 2587.tar.gz mss:/data/db/mysql/ |
6:从库解tar包
1 |
[root@mss mysql]# tar -zxvf 2587.tar.gz |
7:从库修改server_uuid,因为要做主从,所以server_uuid不能相同
1 2 3 4 5 6 7 |
[root@mss 2587]# pwd /data/db/mysql/2587 [root@mss 2587]# ls |grep auto.cnf auto.cnf [root@mss 2587]# ## 随便改动一个数字即可 [root@mss 2587]# vi auto.cnf |
8:主从服务器,修改参数,并重启数据库使其生效
主要说的是log-bin=mysql-bin、server-id=100这两个参数,具体详见:http://www.xxf-home.net/?p=647
9:在主服务器上创建复制账号并授权
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> create user 'mysync'@'192.168.31.%' identified by 'oracle'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> grant replication slave on *.* to 'mysync'@'192.168.31.%'; Query OK, 0 rows affected (0.00 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> |
10:主服务器查看状态
1 2 3 4 5 6 7 8 9 |
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 2751 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> |
11:配置启动从库
1 2 3 4 5 6 7 8 9 10 11 |
[root@mss ~]# mysql -uroot -ppassword ## 配置从服务器的相关参数,即为主服务器的IP、端口、复制账号、密码、日志文件及pos mysql> CHANGE MASTER TO MASTER_HOST='192.168.31.100', MASTER_PORT=2587, MASTER_USER='mysync', MASTER_PASSWORD='oracle', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2751; Query OK, 0 rows affected, 2 warnings (0.00 sec) ## 启动复制功能 mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> |
12:查看从库状态
1 |
mysql> show slave status\G |