MySQL主从复制 实践

mysql> grant all on *.* to root@192.168.1.100 identified by '000000' with grant option;

# 查看帮助信息？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';

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             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

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

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)

# 从库：show variables like '%slave_par%';
set global slave_parallel_workers=10; 设置 sql 线程数为 10
#重启 slave
stop slave;
start slave;
#查看线程
show processlist;(十个 worker 线程)

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 表)

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.sql
3) 配置从 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\G
show processlist;
4) 联级复制测试
主库：create database db3;
从 1：show databases;(获得新建库)
从 2：show databases;(获得新建库)