规划：
172.19.4.50 master
172.19.4.51 slave
172.19.4.52 slave

安装mysql
版本：8.0.12
安装过程（略，参考安装mysql 8文档）

配置主从服务

修改master配置文件vim /etc/my.cnfhttp://www.cndba.cn/asker/article/3785

[mysqld]
log-bin=mysql-bin-9000  ----启用二进制日志
binlog_format=mixed  ----日志有三种格式，分别为Statement,MIXED,以及ROW，默认是Statement
server-id  = 9000    ----数据库唯一标识，默认是1，一般取IP最后一段
#bing-address=0.0.0.0
port=3306
log-slave-updates=1
binlog-do-db =new_test---要记录的数据库名，多个可换行多次设置
replicate-do-db =new_test    ---(要复制的数据库名，多个可换行多次设置
binlog-ignore-db=mysql      ---不对mysql库进行日志记录操作 如下意思雷同
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db= mysql    ---不对test进行复制操作 如下意思雷同
replicate-ignore-db= sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

修改slave配置文件vim /etc/my.cnfhttp://www.cndba.cn/asker/article/3785

[mysqld]
port=3306
#bing-address=0.0.0.0
log-bin=mysql-bin-9001 --根据id命名方便区分
binlog_format=mixed  
server-id=9001 --每个节点id都唯一
read_only=1
relay_log =/apps/mysql/log/mysql-relay-bin
log_slave_updates = 1
binlog-do-db =new_test
replicate-do-db =new_test
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db=sys
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

备库创建数据库和用户并赋权

create database new_test;
create user 'test'@'%' identified with mysql_native_password by 'test';
grant all privileges on new_test.* to 'test'@'%' with grant option;
flush privileges;

创建复制用户http://www.cndba.cn/asker/article/3785

create user 'rep'@'172.18.%' identified with mysql_native_password by 'Dky78&cF';
grant replication slave on *.* to 'rep'@'172.18.%';
flush privileges;

主库恢复到备库

主库锁定所有表

mysql> FLUSH TABLES WITH READ LOCK;

备份数据库

mysqldump -uroot -proot001 new_test >/appl/backup/new_test.sql

通过scp将备份文件传到slave端（两个节点都要）
slave端进行数据恢复

mysql>use new_test
mysql>source /appl/backup/new_test.sql;

取消主库表锁定http://www.cndba.cn/asker/article/3785

mysql> UNLOCK TABLES;

开启主从同步

查询主库log_file和log_poshttp://www.cndba.cn/asker/article/3785

mysql> show master logs;
+-----------------------+-----------+
| Log_name              | File_size |
+-----------------------+-----------+
| mysql-bin-9000.000001 | 465767 |
+-----------------------+-----------+
1 row in set (0.00 sec)

从库执行chang master操作

mysql> CHANGE MASTER TO MASTER_HOST='172.19.4.50',MASTER_USER='rep',MASTER_PASSWORD='Dky78&cF',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin-9000.000001',MASTER_LOG_POS= 465767;

启动从库,查看同步状态http://www.cndba.cn/asker/article/3785

mysql> start slave;
mysql> show slave status/G

至此，完成主从搭建http://www.cndba.cn/asker/article/3785

版权声明：本文为博主原创文章，未经博主允许不得转载。

mysql主从