mysql 主從複製 實踐

• 備份還原
• • 使用mysqldump或者xtrabackup
  • 把主庫現有基礎數據還原到從庫
• 授權
• • grant replication slave on *.*
  • 給從庫一個複製binlog的賬號
• 配置複製，並啟動
• • 從庫上配置複製信息，並指向master
• 查看主從複製信息
• • show slave status \G

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

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

mysql> grant replication slave on *.* to repl@192.168.1.100 identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

#查看幫助信息
？ 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             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

• 主從server_id應該不同；
• 主從開啟binlog日誌
• MASTER_LOG_FILE指定主庫bin_log第一個文件；

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

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;(獲得新建庫)

#查看狀態
show slave status；

• 手動處理
• 跳過複製錯誤：set global sql_slave_skip_counter=1