mysql 完全備份
1,啟用二進(jìn)制日志,并于數(shù)據(jù)庫(kù)分離,單獨(dú)存放
添加
log_bin=/data/bin/mysql-bin
創(chuàng)建/data/bin文件夾并授權(quán)
chown mysql.mysql /data/bin
2,完成備份數(shù)據(jù)庫(kù)
mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
3,對(duì)數(shù)據(jù)庫(kù)進(jìn)行增刪改
INSERT hellodb.students(stuid,name,gender,age) VALUE(27,'Lujunyi','M',30);
4,停止MySQL
systemctl stop mariadb.service
5,解壓備份文件
6,查找完全備份時(shí)二進(jìn)制日志的位置
vim /data/all.sql
HANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=468
7,導(dǎo)出完成備份后的二進(jìn)制日志
mysqlbinlog --start-position=468 /data/bin/mysql-bin.000001 > /data/inc.sql
8,還原數(shù)據(jù)
mysql -e 'source /data/all.sql'
mysql -e 'source /data/inc.sql'
9,驗(yàn)證完成。
誤刪除的恢復(fù)
1,啟用二進(jìn)制日志并與數(shù)據(jù)庫(kù)分開存放
添加
log_bin=/data/bin/mysql-bin
創(chuàng)建/data/bin文件夾并授權(quán)
chown mysql.mysql /data/bin
2,對(duì)數(shù)據(jù)庫(kù)進(jìn)行完全備份
mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
3,對(duì)數(shù)據(jù)庫(kù)進(jìn)行增刪改
mysql -e "drop table hellodb.students"
mysql -e "insert hellodb.teachers value (5,'wangqi',50,'M')"
4,停止服務(wù)
systemctl stop mariadb.service
5,刪除數(shù)據(jù)庫(kù)
6,解壓備份文件
7,查看備份文件,查找二進(jìn)制節(jié)點(diǎn)
vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=521902;
8,導(dǎo)出二進(jìn)制日志節(jié)點(diǎn)數(shù)據(jù)
mysqlbinlog --start-position=521902 /data/bin/mysql-bin.000004 >/data/inc.sql
9,刪除二進(jìn)制日志節(jié)點(diǎn)數(shù)據(jù)中誤操作的命令
vim /data/inc.sql
DROP TABLE `hellodb`.`students` /* generated by server */
10,啟動(dòng)服務(wù)
systemctl start mariadb.service
11.關(guān)閉二進(jìn)制日志記錄
mysql -e "SET sql_log_bin=off"
12,導(dǎo)入備份數(shù)據(jù)
mysql /data/all.sql
mysql /data/inc.sql
13,驗(yàn)證完成。
主從復(fù)制
# 主服務(wù)器
1,主服務(wù)器啟用二進(jìn)制日志,并更改二進(jìn)制目錄
vim /etc/my.cnf
log_bin=/data/bin/mysql-bin
binlog-format=row
server-id=1(主從服務(wù)器必需保證不同)
更改目錄見上面
2,重啟服務(wù)
3,創(chuàng)建一個(gè)賬戶用來(lái)復(fù)制數(shù)據(jù)的賬戶
mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"
4,查看主服務(wù)器正在使用的二進(jìn)制日志
show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 14383 |
+------------------+-----------+
1 row in set (0.00 sec)
# 從服務(wù)器
5,更配置
vim /etc/my.cnf
server-id=2
read-only
#log-bin=/data/bin/mysql-bin
6,啟動(dòng)服務(wù)
7,關(guān)聯(lián)主服務(wù)
MariaDB [(none)]>
CHANGE MASTER TO MASTER_HOST='172.22.7.70', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=14383;
8,查看從服務(wù)器狀態(tài)
9,啟動(dòng)線程
#測(cè)試
10,增刪改主服務(wù)器數(shù)據(jù),查看從服務(wù)器數(shù)據(jù)是否同步。
主從復(fù)制出錯(cuò)的解決-sql_slave_skip_counter
#master服務(wù) ip=172.22.7.70
1,主服務(wù)器啟用二進(jìn)制日志,并更改二進(jìn)制目錄
vim /etc/my.cnf
log_bin=/data/bin/mysql-bin
binlog-format=row
server-id=1(主從服務(wù)器必需保證不同)
更改目錄見上面
2,重啟服務(wù)
3,創(chuàng)建一個(gè)賬戶用來(lái)復(fù)制數(shù)據(jù)的賬戶
mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"
4,查看主服務(wù)器正在使用的二進(jìn)制日志
mysql -e 'show master logs;'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 245 |
+------------------+-----------+
#slave服務(wù) ip=172.22.7.71
5,修改配置文件,啟動(dòng)服務(wù)
vim /etc/my.cnf
[mysqld]
server-id = 2
read-only
systemctl start mariadb
#配置錯(cuò)誤master服務(wù)信息
6,配置,change master to
CHANGE MASTER TO
MASTER_HOST='172.22.7.77',
MASTER_USER='wang',
MASTER_PASSWORD='lodman',
MASTER_PORT=3306,
MASTER_LOG_FILE=log-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
7,查看slave 狀態(tài)
mysql -e 'show slave status\G'
Slave_IO_State:
Master_Host: 172.22.7.77
Master_User: wang
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: log-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: ct7m1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: log-bin.001
Slave_IO_Running: No
Slave_SQL_Running: No
·········略
8,啟動(dòng)復(fù)制線程
9,再次查看slave狀態(tài)
mysql -e 'show slave status\G'
Slave_IO_State:
Master_Host: 172.22.7.77
Master_User: wang
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: log-bin.001
Read_Master_Log_Pos: 4
Relay_Log_File: ct7m1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: log-bin.001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
·········略
10,master服務(wù)增刪改數(shù)據(jù)
11,查看slave服務(wù)是否同步,失?。?/p>
#解決錯(cuò)誤
12,停止并重置slave復(fù)制線程服務(wù)
mysql -e 'stop slave'
mysql -e 'reset slave'
13,配置正確的change master to 信息
CHANGE MASTER TO MASTER_HOST='172.22.7.70',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;
14,查看slave狀態(tài)
show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 172.22.7.70
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 7382
Relay_Log_File: ct7m1-relay-bin.000002
Relay_Log_Pos: 540
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
15,發(fā)現(xiàn)slave狀態(tài)中Slave_SQL_Running: No ,執(zhí)行下面命令更為為YES
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.22.7.70
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 7382
Relay_Log_File: ct7m1-relay-bin.000003
Relay_Log_Pos: 540
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
·········略
16,查看slave服務(wù)數(shù)據(jù)是否同步
17,同步完成。
Mysql 級(jí)聯(lián)復(fù)制
在生產(chǎn)換進(jìn)中有一種主從復(fù)制的方法主節(jié)點(diǎn)先將數(shù)據(jù)同步到一個(gè)中間的從節(jié)點(diǎn),然后由從節(jié)點(diǎn)給后續(xù)的其他從節(jié)點(diǎn)來(lái)復(fù)制數(shù)據(jù),這種復(fù)制方式稱為級(jí)聯(lián)復(fù)制。
級(jí)聯(lián)復(fù)制的好處是可以極大的減輕主節(jié)點(diǎn)的壓力
級(jí)聯(lián)復(fù)制在配置時(shí)需要在中間節(jié)點(diǎn)上啟用log_slave_updates的選項(xiàng)。
#環(huán)境
服務(wù)器 master slave slave
系統(tǒng) centos7 centos7 centos7
ip 172.22.7.70 172.22.7.70 172.22.7.71
#mater
1,主服務(wù)器啟用二進(jìn)制日志,并更改二進(jìn)制目錄
vim /etc/my.cnf
log_bin=/data/bin/mysql-bin
binlog-format=row
server-id=1(主從服務(wù)器必需保證不同)
更改目錄見上面
2,重啟服務(wù)
3,創(chuàng)建一個(gè)賬戶用來(lái)復(fù)制數(shù)據(jù)的賬戶
mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"
4,查看主服務(wù)器正在使用的二進(jìn)制日志
mysql -e 'show master logs;'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 7488 |
| mysql-bin.000003 | 402 |
+------------------+-----------+
#slave
5,修改配置文件,并創(chuàng)建二進(jìn)制日志目錄
vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mysql-bin
binlog-format=row
read-only
log_slave_updates
server-id=2
更改目錄見上
6,啟動(dòng)服務(wù)
systemctl restart mariadb
7,配置change master to信息
CHANGE MASTER TO MASTER_HOST='172.22.7.70',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=402;
8,啟動(dòng)slave線程
9,查看slave狀態(tài)
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.22.7.70
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 7539
Relay_Log_File: ct7m1-relay-bin.000002
Relay_Log_Pos: 7677
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
10,在master上增刪改數(shù)據(jù)測(cè)試查看
#slave1
11,在slave上將slave上的數(shù)據(jù)拷貝過(guò)來(lái)
mysqldump -A --single-transaction -F --master-data=1 > /data/all.sql
scp /data/all.sql 172.22.7.72:/data
12,slave創(chuàng)建一個(gè)賬戶用來(lái)復(fù)制數(shù)據(jù)的賬戶
mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"
13,修改slave1配置
vim /etc/my.cnf
[mysqld]
read-only
server-id=3
14,啟動(dòng)服務(wù)
15,查看slave服務(wù)器的二進(jìn)制日志
mysql -e 'show master logs'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 351 |
| mysql-bin.000002 | 351 |
| mysql-bin.000003 | 351 |
| mysql-bin.000004 | 25552 |
| mysql-bin.000005 | 586 |
+------------------+-----------+
16,打開all.sql文件對(duì)change master on信息加以修改
CHANGE MASTER TO MASTER_HOST='172.22.7.71',MASTER_USER='repluser',MASTER _PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=586;
17,導(dǎo)入slave數(shù)據(jù)
18,啟動(dòng)線程
19,查看slave狀態(tài)
mysql -e "show slave status\G;"
20,對(duì)master對(duì)象增刪改操作,查看是否同步
21,同步完成。
MySQL半同步
異步復(fù)制是當(dāng)用戶寫入一條記錄時(shí),先將數(shù)據(jù)寫入到主節(jié)點(diǎn),然后回復(fù)用戶一個(gè)寫入成功的消息,然后慢慢的將數(shù)據(jù)復(fù)制到其背后的其他從節(jié)點(diǎn),這樣的好處是效率比較高,但是缺點(diǎn)也是非常明顯,主服務(wù)器和從服務(wù)器的延遲過(guò)大并且主服務(wù)器突然發(fā)生異常,此時(shí)就會(huì)造成數(shù)據(jù)的丟失。
同步復(fù)制是當(dāng)用戶寫入一條記錄時(shí),主節(jié)點(diǎn)將數(shù)據(jù)寫入數(shù)據(jù)庫(kù),然后將數(shù)據(jù)復(fù)制給其后面的其他從節(jié)點(diǎn),當(dāng)所有的從節(jié)點(diǎn)返回?cái)?shù)據(jù)復(fù)制成功后,主節(jié)點(diǎn)再回復(fù)用戶數(shù)據(jù)接入成功的消息,這樣做的好處是,確保了數(shù)據(jù)的安全性,但損失了效率。
半同步復(fù)制是間于同步復(fù)制和異步復(fù)制之間的一種復(fù)制方法,他的工作原理是:當(dāng)用戶執(zhí)行寫操作時(shí),主節(jié)點(diǎn)會(huì)將數(shù)據(jù)發(fā)送給其后面的其他從節(jié)點(diǎn),只要有一個(gè)從節(jié)點(diǎn)返回復(fù)制成功的消息,主節(jié)點(diǎn)就直接返回寫入成功,如果主節(jié)點(diǎn)背后的從節(jié)點(diǎn)遲遲不返回復(fù)制成功消息,此時(shí)就會(huì)有一個(gè)超時(shí)時(shí)長(zhǎng),一旦達(dá)到超時(shí)時(shí)長(zhǎng),主節(jié)點(diǎn)就先返回消息告訴用戶復(fù)制成功,而后將數(shù)據(jù)繼續(xù)給從節(jié)點(diǎn)復(fù)制。
#配置主從復(fù)制 步驟見上。
#配置半同步
##master
1,修改配置文件啟用插件
vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mariadb-bin
binlog-format=row
server-id=1
rpl_semi_sync_master_enabled
2,重啟服務(wù),
systemctl restart mariadb
3,查看插件是否啟動(dòng)
SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 3000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
4,設(shè)置超時(shí)時(shí)長(zhǎng)
SET GLOBAL rpl_semi_sync_master_timeout=3000;
Query OK, 0 rows affected (0.00 sec)
##slave
5,修改配置文件啟用插件
vim /etc/my.cnf
[mysqld]
log-bin=/data/bin/mariadb-bin
binlog-format=row
server-id=2
rpl_semi_sync_master_enabled
6,重啟服務(wù),
systemctl restart mariadb
7,查看插件是否啟動(dòng)
SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+--------------+
| Variable_name | Value |
+------------------------------------+--------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
+------------------------------------+--------------+
5 rows in set (0.00 sec)
8,啟動(dòng)復(fù)制線程
9,測(cè)試檢查
10,同步完成
以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
您可能感興趣的文章:- MySQL半同步復(fù)制原理配置與介紹詳解
- 簡(jiǎn)單談?wù)凪ySQL的半同步復(fù)制
- Mysql半同步復(fù)制原理及問(wèn)題排查
- 深入解析半同步與異步的MySQL主從復(fù)制配置
- 詳解MySQL的半同步