MySQL主从复制 实践
异步主从复制
主从部署步骤:
- 备份还原
- 使用mysqldump或者xtrabackup
- 把主库现有基础数据还原到从库
- 授权
- grant replication slave on *.*
- 给从库一个复制binlog的账号
- 配置复制,并启动
- 从库上配置复制信息,并指向master
- 查看主从复制信息
- show slave status \G
1)备份还原
主:101
从:100
a)主库备份
aiapple@Ubuntu:~$ mysqldump -uroot -p --socket=/tmp/mysqldata/node1/mysql.sock --master-data --all-databases > all_master.sql --master-date:记录备份时binlog位置 aiapple@ubuntu:~$ cat all_master.sql | less -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=6125; -- -- Current Database: `db1` --
b)从库还原
主库远程连接到从库,使用source还原
从库添加主库白名单:
mysql> grant all on *.* to [email protected] WITH GRANT OPTION;
设置密码:
mysql> set password for root@'localhost'=password('000000');
Query OK, 0 rows affected (0.00 sec)
主库添加从库白名单:
mysql> grant all on *.* to [email protected] identified by '000000' with grant option;
主库登陆从库并还原数据:
aiapple@ubuntu:~$ mysql -uroot -p -h 192.168.1.100 mysql> source all_master.sql
2)赋权 replication slave
mysql> grant replication slave on *.* to [email protected] identified by 'repl'; Query OK, 0 rows affected (0.00 sec)
3) 从库配置复制
#查看帮助信息 ? change master to mysql> change master to MASTER_USER='repl'; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> change master to MASTER_PASSWORD='repl'; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> change master to MASTER_HOST='192.168.1.101'; Query OK, 0 rows affected (0.03 sec) mysql> change master to MASTER_LOG_FILE='mysql-bin.000001';
4)启动:
mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; slave_io_running:yes slave_sql_running:yes #表示配置成功 mysql> show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | init | show processlist | | 16 | system user | | NULL | Connect | 60 | Waiting for master to send event | NULL | | 17 | system user | | NULL | Connect | 60 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
注意:
- 主从server_id应该不同;
- 主从开启binlog日志
- MASTER_LOG_FILE指定主库bin_log第一个文件;
半同步复制
配置mysql半同步复制 semi-sync
查看有哪些插件
show plugin
1.主从异步复制搭建
1)主库全备,备库恢复 mysqldump -uroot -p123456 --socket=/data/mysql/node1/mysqld.sock --single-transaction -A --master-data=1 > all_db.sql mysql -utest -ptest -h(从库IP) -P3306 mysql>source all_db.sql; 2)主库授权用户 grant replication slave on *.* to repl@'(从库IP)' identified by 'repl'; 3)从库配置复制 less all_db.sql|grep "change master to" change master to master_host='(主库IP)',master_user='repl',master_password='repl',master_log_file='XXX',master_log_pos=XXX; start stave; show slave status\G 4)复制检验 主库: use db1; insert into t1 values(10); 从库: use db1; select * from t1;(获得数据) 主库: drop database db2; 从库: show databases;(显示db2被删除) 5)查看线程 主库:show processlist;(dump线程) 从库:show processlist;(IO线程、SQL线程) 6)查看日志 从库:cd /data/mysql/node1 cat master.info cat relay-log.info
2.主从半同步复制安装
1)主库安装插件 show plugins; install PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 2)从库安装插件 show plugins; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 3)参数设置 主库: show variables like '%semi%'; SET GLOBAL rpl_semi_sync_master_enabled=1; 从库: SET GLOBAL rpl_semi_sync_slave_enabled=1; 4)重启主从复制 从库: stop slave; start slave; 5)状态检查 show global status like '%semi%'; 6)复制检查 主库: use db1; insert into t1 values(100); 从库: use db1; select * from t1;(获得数据) 7)测试延迟 从库: stop slave; 主库: use db1; insert into t1 values(1);(被卡10s) set global rpl_semi_sync_master_timeout=1000;(设置主等从时间1秒) 从库: start slave; stop slave; 主库: use db1; insert into t1 values(88);(被卡1s)
注意:rpl_semi_sync_master_timeout主库等待时间不能设置大,不然会引起主库雪崩效应;最好在1秒内;
并行复制
1.MySQL并行复制
相关推荐
emmm00 2020-11-17
暗夜之城 2020-11-11
疯狂老司机 2020-09-08
CoderToy 2020-11-16
王艺强 2020-11-17
ribavnu 2020-11-16
bianruifeng 2020-11-16
wangshuangbao 2020-11-13
苏康申 2020-11-13
vivenwan 2020-11-13
moyekongling 2020-11-13
云中舞步 2020-11-12
要啥自行车一把梭 2020-11-12
aydh 2020-11-12
kuwoyinlehe 2020-11-12
minerk 2020-11-12
vitasfly 2020-11-12
jazywoo在路上 2020-11-11