MySQL主從介紹
MySQL主從又叫做Replication、AB複制。簡單講就是A和B兩台機器做主從後,在A上寫資料,另外一台B也會跟着寫資料,兩者資料實時同步。MySQL主從是基于binlog的,主上須開啟binlog才能進行主從。
主從過程:
需要保持資料的一緻性,才能複制資料
1、主伺服器将更改操作記錄到binlog中
2、從伺服器将主伺服器的binlog事件(SQL語句)同步到本機(從伺服器)并記錄在relaylog(中繼日志)中
3、從伺服器根據relaylog裡面的SQL語句按順序執行
說明: 該過程有三個線程,主上有一個log dump線程,用來和從的i/o線程傳輸binlog;從上有兩個線程,其中i/o線程用來同步主的binlog并生成relaylog,另外一個SQL線程用來把relaylog裡面的SQL語句執行。
應用的場景:
1、備份重要的資料
2、分擔主庫伺服器的讀的壓力,讓從伺服器讀,主伺服器寫操作
準備工作
主伺服器:192.168.3.74 安裝mysql5.6
從伺服器:192.168.3.83 安裝mysql5.6
配置主
1、[root@centos7 system]# vi /etc/my.cnf
[mysqld]
server-id=132
log_bin=jacklinux
2、重新開機:
[root@centos7 system]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@centos7 system]# ls -lt /data/mysql/ #二進制檔案,不能檢視
-rw-rw---- 1 mysql mysql 19 Nov 22 09:33 jacklinux.index
-rw-rw---- 1 mysql mysql 120 Nov 22 09:33 jacklinux.000001
3、建立一個資料庫為試驗做準備:
[root@centos7 mysql]# mysqldump -uroot -p123456 zrlog > /tmp/zrlog.sql
[root@centos7 mysql]# mysql -uroot -p123456 -e "create database jacktest"
[root@centos7 mysql]# mysql -uroot -p123456 jacktest < /tmp/zrlog.sql
4、建立同步賬号
mysql> grant replication slave on *.* to 'repl'@'192.168.3.83' identified by '123456';
#隻給複制權限,對所有庫所有表,ip為從伺服器的ip,賬号為repl,密碼:123456
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
#先進行鎖表,等主從伺服器資料保持一緻,再解鎖表
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| jacklinux.000001 | 10989 | | | |
#需要記錄file、position位置
配置從
1、[root@test ~]# vim /etc/my.cnf
server-id=130
#必須得和主伺服器上的server-id不一樣
2、[root@test ~]# scp 192.168.3.74:/tmp/*.sql .
3、mysql> create database zrlog;
4、恢複資料庫
[root@test ~]# mysql -uroot zrlog < zrlog.sql
實作主從同步:
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.3.74',master_user='repl',master_password='123456',master_log_file='jacklinux.000001',master_log_pos=10989;
#master_log_file、master_log_pos:主伺服器上
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5、解鎖主庫的表(在主上操作):
mysql> unlock tables;
此時主從複制搭建完成,下一步驗證。
測試主從同步
主伺服器:
binlog-do-db= 僅同步指定的庫
binlog-ignore-db= 忽略指定的庫
從伺服器:
replicate_do_db= 同步指定的庫
replicate_ignore_db= 忽略指定的庫
replicate_do_table= 同步指定的表
replicate_ignore_table= 忽略指定的表
replicate_wild_do_table= 如aming.%,支援通配符
replicate_wild_ignore_table= 忽略
驗證:
主伺服器上建立表zrtest,并插入資料
mysql> use zrlog
mysql> create table zrtest(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> desc zrtest;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | YES | | NULL | |
1 row in set (0.01 sec)
mysql> insert zrtest value(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert zrtest value(20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from zrtest;
+------+
| id |
| 1 |
| 20 |
從伺服器上檢視是否有zrtest表,還有資料
mysql> use zrlog;
驗證完成,可以同步
主資料庫伺服器上:
1、鎖表:
2、等還原後記得解鎖
mysql> unlock tables;
斷電後導緻主從不一緻,需要重新連接配接,此操作不用停服務或者鎖表
準備操作:
1、需要開啟gtid
[root@centos7 db_bk]# vi /etc/my.cnf
server-id=133
binlog_format=mixed
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
2、重新開機mysqld,再表裡插入幾行資料
3、檢視:
show global variables like '%gtid%'
從伺服器上:
1、vi /etc/my.cnf
2、重新開機mysqld
操作:
1.安裝innobackupex
yum list | grep percona
yum install percona-xtrabackup
2.資料庫配置寫下使用者名密碼
1、備份源資料庫
[root@centos7 ~]# innobackupex --socket=/tmp/mysql.sock /data/db_bk/
2、檢視目錄
3、應用日志,使資料檔案保持一緻
[root@centos7 ~]# innobackupex --apply-log /data/db_bk/2018-01-03_16-39-52/
4、拷貝資料
[root@centos7 db_bk]# cd /data/db_bk/
[root@centos7 db_bk]# scp -r 2018-01-03_16-39-52/ [email protected]:/data
從資料庫伺服器上
停止資料庫服務
[root@test data]# /etc/init.d/mysqld stop
清空檔案夾資料
[root@test mysql]# rm -rf /var/lib/mysql/*
恢複資料
[root@test mysql]# innobackupex --defaults-file=/etc/my.cnf --copy-back /data/2018-01-03_16-39-52/
改權限,并啟動資料庫
[root@test mysql]# chown -R mysql.mysql /var/lib/mysql
/etc/init.d/mysqld start
主資料庫伺服器上
檢視master的gtid位置:
[root@centos7 db_bk]# cat /data/db_bk/2018-01-03_16-39-52/xtrabackup_binlog_info
jacklinux.000014127744e9aeff-c530-11e7-9e06-0050562d75e7:1-4
連接配接slave,設定跳過的日志檔案
mysql
mysql> set global gtid_purged='44e9aeff-c530-11e7-9e06-0050562d75e7:1-4';
mysql> change master to master_host='192.168.3.74',master_user='repl',master_password='123456',master_port=3306,master_auto_position=1;
啟動從庫配置
> start slave;
檢視狀态
> show slave status\G;
報錯:Slave_IO_Running: No
解決:查了很久,原來是我的change master to master_host='192.168.3.83',master_user='repl',master_password='123456',master_port=3306,master_auto_position=1;中的master_host寫成本機了,應該是192.168.3.74
記得解鎖
本文轉自 jiekegz 51CTO部落格,原文連結:http://blog.51cto.com/jacksoner/1983983