程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql 主從復制實踐，mysql主從

mysql 主從復制實踐，mysql主從

編輯：MySQL綜合教程

mysql 主從復制實踐，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並行復制

#從庫：
show variables like '%slave_par%';
set global slave_parallel_workers=10; 設置sql線程數為10
#重啟 slave
stop 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.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；

成功與否： slave_sql_running: slave_io_running: 延時多久 seconds_Behind_Master 從庫出現問題時 last_sql_errno last_sql_error last_io_errno last_io_error 復制出錯處理常見：1062（主鍵沖突），1032（記錄不存在）解決：