MySQL命令操作实现主从复制
程序员文章站
2022-08-06 23:11:06
环境规划 | 主机名 | 角色 | IP地址 | | : : | : : | : : | | db01 | MySQL主库 | 10.0.0.51 | | db02 | MySQL从库 | 10.0.0.52 | 2.所有MySQL都开启binlog功能,确保所有MySQL的serv ......
1.环境规划
主机名 | 角色 | ip地址 |
---|---|---|
db01 | mysql主库 | 10.0.0.51 |
db02 | mysql从库 | 10.0.0.52 |
2.所有mysql都开启binlog功能,确保所有mysql的server-id不同
主库: [mysqld] basedir=/application/mysql datadir=/application/mysql/data socket=/application/mysql/tmp/mysql.sock port=3306 server_id=10 log-error=/var/log/mysql.log log-bin=/data/binlog/mysql-bin sync_binlog=1 binlog_format=row 从库: [mysqld] basedir=/application/mysql datadir=/application/mysql/data socket=/application/mysql/tmp/mysql.sock port=3306 server_id=11 log-error=/var/log/mysql.log log-bin=/data/binlog/my-bin sync_binlog=1 binlog_format=row
3.主库授权主从复制用户
mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by '123456';
4.主库将数据库数据做全备,然后将备份文件推送到从库
[root@db01 ~]# mysqldump -uroot -p123 -a -b -r --master-data=2 --single-transaction |gzip >/backup/full_$(date +%f).sql.gz [root@db01 ~]# scp /backup/full_2020-04-09.sql.gz root@10.0.0.52:/backup
5.从库将备份文件恢复到数据库
[root@db02 ~]# gunzip /backup/full_2020-04-09.sql.gz mysql> source /backup/full_2020-04-09.sql mysql> show databases;
6.从库上找到binlog位置点
[root@db02 ~]# sed -n '22p' /backup/full_2020-04-09.sql
7.从库配置主从复制参数
mysql> change master to -> master_host='10.0.0.51', -> master_port=3306, -> master_user='rep', -> master_password='123456', -> master_log_file='mysql-bin.000008', -> master_log_pos=1100; mysql> start slave; mysql> show slave status\g
8.主库创建数据库,然后到从库验证数据是否同步
db01: mysql> create database test1; db02: mysql> show databases;
转自:https://www.cnblogs.com/yanyanqaq/p/12675842.html
上一篇: 用得着这么装逼吗?
下一篇: 李孝恭为唐朝打下半壁*,最后结果怎么样?