MySQL主从复制实践

异步主从复制

主从部署步骤:

备份还原
- 使用mysqldump或者xtrabackup
- 把主库现有基础数据还原到从库
授权
- grant replication slave on *.*
- 给从库一个复制binlog的账号
配置复制，并启动
- 从库上配置复制信息，并指向master
查看主从复制信息
- show slave status G

1）备份还原主：101从：100a）主库备份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 root@192.168.1.101 WITH GRANT OPTION; 设置密码：mysql> set password for root@"localhost"=password（"000000"）;Query OK, 0 rows affected （0.00 sec）主库添加从库白名单：mysql> grant all on *.* to root@192.168.1.100 identified by "000000" with grant option;主库登陆从库并还原数据：aiapple@ubuntu:~$ mysql -uroot -p -h 192.168.1.100 mysql> source all_master.sql2）赋权 replication slavemysql> grant replication slave on *.* to repl@192.168.1.100 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 statusG; slave_io_running:yesslave_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 plugin1.主从异步复制搭建1）主库全备，备库恢复mysqldump -uroot -p123456 --socket=/data/mysql/node1/mysqld.sock --single-transaction -A --master-data=1 > all_db.sqlmysql -utest -ptest -h（从库IP） -P3306mysql>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 statusG4）复制检验主库：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/node1cat master.infocat relay-log.info2.主从半同步复制安装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并行复制 #从库：show variables like "%slave_par%";set global slave_parallel_workers=10; 设置sql线程数为10#重启 slavestop slave;start slave;#查看线程show processlist;（十个worker线程）注：实际上有11个线程，10worker线程，1个调度线程；部分数据复制

部分数据复制：在配置文件中设置主库添加参数指定到库：binlog_do_db=db1binlog_ignore_db=db1binlog_ignore_db=db2或从库添加参数可以到表；replicate_do_db=db1replicate_ignore_db=db1replicate_do_table=db1.t1replicate_wild_do_table=db%.% #配置设置replicate_wild_ignore_table=db1.% #1打头的表注：建议使用在从库添加参数，这样更加灵活可以指定到表级别；部分复制 1）主库：create database db2;2）从库部分复制配置配置文件中增加配置#vim /data/mysql/my1.cnf（replicate_do_db=db2）重启mysql#mysqladmin -uroot --socket=XXX --port=3306 -p123456 shutdown#/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/my1.cnf & show slave status;显示：replicate_do_db=db2 3）测试主库：use db1;delete from t1;从库：use db1;select * from t1;（任然保留数据）主库：use db2;create table user（a int,b int）;从库：use db2;show tables;（查看到user表）联级复制

联级复制（A->B->C）1）从库：#vim /data/mysql/my1.cnf（log_slave_updates）#mysqladmin -uroot --socket=XXX --port=3306 -p123456 shutdown#/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/my1.cnf &2）创建新实例在主库服务器上创建一个从库2实例#mysqladmin -uroot --socket=XXX --port=3306 -p123456 shutdown#kill -9 （mysqld_safe进程号）#cp -r node1 node2#vim my.cnf（修改相关参数,端口3307）#chown -R mysql.mysql node2#/usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/mysql/my1.cnf &#/usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/mysql/my2.cnf &#mysqldump -utest -ptest -hXXX -P3306 -A --master-data=1 > d731.sql（dump从库1的全备）#mysql -uroot --socket=/data/mysql/node2/mysqld.sock -p123456 < d731.sql3）配置从1和从2的主从从1授权：grant replication slave on *.* to repl@"（从2IP）" identified by "repl";从2配置复制：less d731.sql|grep "change master to"change master to master_host="（从1IP）",master_user="repl",master_password="repl",master_log_file="XXX",master_log_pos=XXX;start stave;show slave statusGshow processlist;4）联级复制测试主库：create database db3;从1：show databases;（获得新建库）从2：show databases;（获得新建库）监控与处理

#查看状态
show slave status；成功与否：slave_sql_running:slave_io_running:延时多久seconds_Behind_Master从库出现问题时last_sql_errnolast_sql_errorlast_io_errnolast_io_error复制出错处理常见：1062（主键冲突），1032（记录不存在）解决：

手动处理
跳过复制错误：set global sql_slave_skip_counter=1

复制出错，大部分因为，主从数据不一致导致的；最好的办法还是进行数据主从复制的校验；percona公司有主从复制校验工具；本文永久更新链接地址

首页 / 数据库 / MySQL / MySQL主从复制 实践

首页 / 数据库 / MySQL / MySQL主从复制实践