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 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.sql

2）赋权 replication slave

mysql> 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 status\G; 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 plugin

1.主从异步复制搭建

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 status\G4)复制检验主库：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.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并行复制

#从库：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=db1
binlog_ignore_db=db1
binlog_ignore_db=db2

或从库添加参数
可以到表；
replicate_do_db=db1
replicate_ignore_db=db1
replicate_do_table=db1.t1
replicate_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 status\Gshow 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_errno
last_sql_error
last_io_errno
last_io_error

复制出错处理
常见：1062（主键冲突），1032（记录不存在）
解决：