Ausführliche Erläuterung der Master-Slave-Replikation, Lese-/Schreibtrennung, Sicherung und Wiederherstellung von MySQL

1. MySQL-Master-Slave-Replikation

1. Einführung

Warum verwenden wir die Master-Slave-Replikation?

Zweck der Master-Slave-Replikation:

kann eine Echtzeitsicherung der Datenbank durchführen, um die Datenintegrität sicherzustellen.

kann Lesen und Schreiben trennen, der Master-Server schreibt nur , und der Slave-Server liest nur, was die Gesamtleistung verbessern kann.

Schematische Darstellung:



Wie aus der obigen Abbildung ersichtlich ist, wird die Synchronisation durch synchrones Lesen und Schreiben von Protokolldateien abgeschlossen.

2. Ändern Sie die Konfigurationsdatei

Betreiben Sie die Maschine zwei Tage lang und stellen Sie sicher, dass die Server-ID lautet unterschiedlich, normalerweise die Haupt-ID sollte kleiner sein als die Sekundär-ID. Achten Sie unbedingt darauf.

# 3306和3307分别代表2台机器
# 打开log-bin,并使server-id不一样
#vim /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1
#vim /data/3307/my.cnf
log-bin = /data/3307/mysql-bin
server-id = 3
#检查
1、
[root@bogon ~]# egrep "log-bin|server-id" /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1
[root@bogon ~]# egrep "log-bin|server-id" /data/3307/my.cnf 
log-bin = /data/3307/mysql-bin
server-id = 3
2、
[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock -e "show variables like 'log_bin';"
Enter password:
+--------+--------+
| Variable_name | Value |
+--------+--------+
| log_bin    | ON |  # ON 为开始开启成功
+--------+--------+

3. Erstellen Sie ein Konto für die Slave-Replikation.

Erstellen Sie normalerweise ein dediziertes Konto für die Master-Slave-Replikation Vergessen Sie nicht, die Genehmigung zu erteilen.

# 主库授权，允许从库来连接我取日志
[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock
Enter password:
# 允许从库192.168.200网段连接，账号rep，密码nick。
mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by 'nick';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 检查创建的rep账号：
mysql> select user,host from mysql.user;
+-----+-------------+
| user | host       |
+-----+--------------+
| root | 127.0.0.1     |
| rep | 192.168.200.%   |
| root | localhost      |
| root | localhost.localdomain |
+-----+------------------+
7  rows in set (0.00 sec)

4. Sichern Sie die Hauptdatenbank und stellen Sie sie in der Slave-Datenbank wieder her.

Sichern Sie die vorhandenen Daten in der Hauptdatenbank und stellen Sie es in der Slave-Datenbank wieder her. Aus der Datenbank sind die Daten der beiden Hosts zu diesem Zeitpunkt konsistent.

Wenn Sie Daten im Voraus haben, vergessen Sie dies nicht.

1) Sperren Sie die Hauptdatenbank, sodass sie nur Leseberechtigungen hat.

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
#5.1、5.5锁表命令略有不同。
# 5.1锁表：flush tables with read lock;
# 5.5锁表：flush table with read lock;

2) Denken Sie an dieser Stelle daran, eine Sicherungskopie anzulegen.

mysql> show master status;
+-------+------+--------+---------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------+------+--------+---------+
| mysql-bin.000013 |  410 |       |        |
+-------+------+--------+---------+
1 row in set (0.00 sec)

3) Fenster klonen und Daten sichern.

[root@bogon ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -A -B --events --master-data=2|gzip >/opt/rep.sql.gz
Enter password:
参数：  -A：备份所有的
#看rep.sql.gz参数
vim /opt/rep.sql.gz
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=410;

4) Überprüfen Sie den Masterstatus, ob der Wert normal ist.

mysql> show master status;
+------+------+---------+-------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------+-----+---------+--------+
| mysql-bin.000013 |  410 |      |        |
+--------+----+---------+--------+
1 row in set (0.00 sec)

5) Bibliothek entsperren

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

6) Zur Slave-Bibliothek wiederherstellen

[root@bogon ~]# gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock
Enter password:

5. Konfigurieren Sie die Slave-Bibliothek und übernehmen Sie

Ändern Sie die Verbindungsparameter der Slave-Bibliothek und der Hauptbibliothek Die Konfiguration wird wirksam. Die Inspektion war erfolgreich!

1) Betreten Sie die Slave-Bibliothek.

[root@bogon ~]# mysql -uroot -p -S /data/3307/mysql.sock
Enter password:

2) Ändern Sie die Parameter, die der Slave-Server für die Verbindung und Kommunikation mit dem Master-Server verwendet.

mysql> CHANGE MASTER TO
   MASTER_HOST='192.168.200.98',
   MASTER_PORT=3306,
   MASTER_USER='rep',
   MASTER_PASSWORD='nick',
   MASTER_LOG_FILE='mysql-bin.000013',
   MASTER_LOG_POS=410;
Query OK, 0 rows affected (0.01 sec)

3) Sehen Sie sich die geänderten Parameter an.

[root@localhost ~]# cd /data/3307/data/
[root@localhost data]# cat master.info
18
mysql-bin.000013
410
192.168.200.98
REP
nick
3306
60
0
0
1800.000
0

4) Wirksam!

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

5) Überprüfen Sie die folgenden Parameter. Wenn sie übereinstimmen, ist das normal!

mysql> show slave status\G
Relay_Master_Log_File: mysql-bin.000013
       Slave_IO_Running: Yes    #取logo。
      Slave_SQL_Running: Yes    #读relay-bin、logo,写数据。
Seconds_Behind_Master: 0    #落后主库的秒数。

6) Relay-bin.logo anzeigen.

[root@localhost 3307]# cd /data/3307
[root@localhost 3307]# ll
总用量 48
drwxr-xr-x. 9 mysql mysql 4096 10月 29 18:52 data
-rw-r--r--. 1 mysql mysql 1900 10月 29 11:45 my.cnf
-rwx------. 1 root root  1307 10月 20 17:06 mysql
-rw-rw----. 1 mysql mysql   6 10月 29 11:00 mysqld.pid
-rw-r-----. 1 mysql mysql 15090 10月 29 18:49 mysql_nick3307.err
srwxrwxrwx. 1 mysql mysql   0 10月 29 11:00 mysql.sock
-rw-rw----. 1 mysql mysql  150 10月 29 18:49 relay-bin.000001
-rw-rw----. 1 mysql mysql  340 10月 29 18:52 relay-bin.000002
-rw-rw----. 1 mysql mysql  56 10月 29 18:49 relay-bin.index
-rw-rw----. 1 mysql mysql  53 10月 29 18:52 relay-log.info

7) Relay-log.info anzeigen.

[root@localhost 3307]# cat relay-log.info
/data/3307/relay-bin.000002
340
mysql-bin.000013
497

8) Master.info anzeigen.

[root@localhost 3307]# cat data/master.info
18
mysql-bin.000013
497
192.168.200.98
rep
nick
3306
60
0
0
1800.000
0

6. Lese-Schreib-Trennung

Lese-Schreib-Trennung ist in Produktionsumgebungen üblich und auch notwendig Fähigkeit.

Ignorieren Sie die Synchronisierung der MySQL-Master-Slave-Replikationsautorisierungstabelle und die Lese-/Schreibtrennung.

[root@bogon 3306]# vim my.cnf
#添加以下四行
replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
server-id = 1

1) Methode, um zu verhindern, dass Daten über den schreibgeschützten Parameter in die Slave-Bibliothek geschrieben werden.

#修改配置文件。
vim /data/3307/my.cnf
[mysqld]
read-only
#对用户授权事不能指定有super或all privileges权限。不然没效果。
#创建账户suoning，并刷新权限。
mysql> grant select,insert,update,delete on *.* to 'suoning'@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#用创建账户登录，并创建库
[root@bogon 3307]# mysql -usuoning -p123 -S /data/3307/mysql.sock
mysql> create user kangkangkang@'192.%' identified by 'old123';
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

2) Master-Slave-Synchronisierungsfehler

A. Last_SQL_Errno: 1007
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
B.忽略
skip-name-resolve    #忽略名字解析
slave-skip-errors = 1032,1062,1007  #忽略故障编号
server-id = 3

3) Bin-Protokoll öffnen von die Bibliothek

vim my.cnf
log-bin = /data/3307/mysql-bin
log-slave-updates      #表示从库记录bin-log
expire_logs_days = 7    #保留7天bin-log。

7. Die Hauptmaschine ist ausgefallen

Was sollen wir tun, wenn die Hauptmaschine ausgefallen ist? ist ausgefallen? Lassen Sie es uns schnell ersetzen und den Verlust minimieren? Natürlich ist auch ein Dual-Machine-Hot-Backup eine gute Wahl, was Ihnen im nächsten Abschnitt erläutert wird.

Wenn in einer Umgebung mit einem Master und mehreren Slaves der Master ausfällt, wählen Sie einen Slave als Master aus und führen Sie die Synchronisierung mit anderen Slaves fort.

A.查看每个从库的master.info，看谁的更靠前，最新，更大，丢的数据最少。
[root@localhost 3307]# cat /data/3307/data/master.info
mysql-bin.000015
326
B.确保所有relay log全部更新完毕。
在每个从库上执行stop slave io_thread;show processlist;
知道看到Has read all relay log;表示从库更新都执行完毕。
C.登陆mysql -uroot -p -S /data/3307/mysql.sock
stop slave;
reset master;
quit
D.进入到数据库目录，删除master.info relay-log.info
cd /data/3307/data/
rm -f master.info relay-log.info
E. 3307提升为主库
vim /data/3307/my.cnf
开启log-bin = /data/3307/mysql-bin
如存在log-slave-updates，read-only等一定要注释。
/data/3307/mysql restart
F.其它从库操作
stop slave;
change master to master_host ='192.168.200.98';
start slave;
show slave status\G

8. Dual-Primär

Verwenden Sie die Hauptprämisse: Der Primärschlüssel der Tabelle wird inkrementiert.

Bei Dual-Mastern sieht die ID wie folgt aus: Die ID wird durch das Programm in die Bibliothek M1: 1, 3, 5 und M2: 2, 4, 6 geschrieben.

[root@localhost 3307]# vim my.cnf
[mysqld]
auto_increment_increment    = 2
auto_increment_offset      = 2
[root@localhost 3307]# ./mysql restart
[root@localhost 3306]# vim my.cnf
[mysqld]
auto_increment_increment    = 2
auto_increment_offset      = 1
log-bin = /data/3306/mysql-bin
log-slave-updates
[root@localhost 3306]# ./mysql restart
[root@localhost 3306]# mysqldump -uroot -pnick -S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql
[root@localhost 3306]# mysql -uroot -pnick -S /data/3306/mysql.sock < /opt/3307bak.sql
mysql> CHANGE MASTER TO
   MASTER_HOST=&#39;192.168.200.98&#39;,
   MASTER_PORT=3307,
   MASTER_USER=&#39;rep&#39;,
   MASTER_PASSWORD=&#39;nick&#39;;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G

2. MySQL-Sicherung und -Wiederherstellung

1 🎜>

Die einfachste Sicherung einer einzelnen Datenbank.

1>语法：mysqldump –u 用户名 –p 数据库名> 备份的数据库名
2>备份nick_defailt数据库，查看内容。
[root@localhost ~]# mysqldump -uroot -p -B nick_defailt >/opt/mysql_nick_defailt.bak
Enter password:
[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_nick_defailt.bak
DROP TABLE IF EXISTS `oldsuo`;
CREATE TABLE `oldsuo` (
 `id` int(4) NOT NULL,
 `name` char(20) NOT NULL,
 `age` tinyint(2) NOT NULL DEFAULT '0',
 `dept` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOCK TABLES `oldsuo` WRITE;
INSERT INTO `oldsuo` VALUES (2,'索宁',0,NULL),(3,'索尼',0,NULL),(4,'底底',0,NULL);
UNLOCK TABLES;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
 `qq` varchar(15) DEFAULT NULL,
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `name` char(20) NOT NULL,
 `suo` int(4) DEFAULT NULL,
 `age` tinyint(2) NOT NULL DEFAULT '0',
 `dept` varchar(16) DEFAULT NULL,
 `sex` char(4) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (NULL,2,'oldsuo',NULL,0,NULL,NULL),(NULL,3,'kangknag',NULL,0,NULL,NULL),(NULL,4,'kangkang',NULL,0,NULL,NULL),(NULL,5,'oldsuo',NULL,0,NULL,NULL),(NULL,6,'kangknag',NULL,0,NULL,NULL),(NULL,7,'kangkang',NULL,0,NULL,NULL);
UNLOCK TABLES;
3>启用压缩备份数据库
[root@localhost~]#mysqldump -uroot -p -B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz
Enter password:
[root@localhost ~]# ll /opt/
总用量 28
-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sq2
-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sql
-rw-r--r--. 1 root root 1002 10月 27 11:55 mysql_nick_defailt.bak
-rw-r--r--. 1 root root 1002 10月 27 11:56 mysql_nick_defailt.bak.gz
-rw-r--r--. 1 root root 3201 10月 27 11:46 mysql_nick_defailt_B.bak
drwxr-xr-x. 2 root root 4096 11月 22 2013 rh
-rw-r--r--. 1 root root 1396 10月 24 16:11 student_bak.sql
4>恢复nick_defailt数据库
[root@localhost ~]# mysql -uroot -p nick_defailt </opt/mysql_nick_defailt.bak 
Enter password:
#加-B恢复方法
[root@localhost ~]# mysql -uroot -p </opt/mysql_nick_defailt_B.bak        
Enter password:
5>总结
1、备份用-B参数。增加use db,和create database的信息。
2、用gzip对备份的数据压缩。

2. Mehrere Datenbanken sichern

Was ist mit dem Sichern mehrerer Datenbanken?

#多个数据库名中间加空格
[root@localhost ~]# mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz
Enter password:

3. Einzelne und mehrere Tabellen sichern

Was soll ich tun, wenn ich einzelne und mehrere Tabellen sichere?

1>语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
[root@localhost ~]# mysqldump -uroot -p nick_defailt student >/opt/mysql_nick_defailt_student.bak
Enter password:
2>语法：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名
[root@localhost ~]# mysqldump -uroot -p nick_defailt student oldsuo >/opt/mysql_nick_defailt.bak    
Enter password:

Parameter von mysqldump

Schlüsselparameter von mysqldump

-B指定多个库，增加建库语句和use语句。
--compact去掉注释，适合调试输出，生产不用。
-A 备份所有库。
-F刷新binlog日志。
--master-data 增加binlog日志文件名及对应的位置点。
-x，--lock-all-tables
-l,--locktables
-d 只备份表结构
-t 只备份数据
--single-transaction 适合innodb事务数据库备份。

5. Inkrementelle Wiederherstellung

Das Wichtigste ist, dass die Produktionsumgebung im Allgemeinen die sogenannte inkrementelle Sicherung und Wiederherstellung verwendet, um weiterhin Daten auf der Grundlage des Originals hinzuzufügen Die Daten müssen nicht jedes Mal erneut hinzugefügt werden, was Zeit und Mühe spart.

A：增量恢复必备条件：
1.开启MySQL数据库log-bin参数记录binlog日志。
[root@localhost 3306]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
2.存在数据库全备。
B：生产环境 mysqldump备份命令：
# 进行数据库全备，（生产环境还通过定时任务每日凌晨执行）
mysqldump -uroot -pnick -S /data/3306/mysql.sock --default-character-set=gbk --single-transaction -F -B nick |gzip >/server/backup/mysql_$(date +%F).sql.gz
# innodb引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A -B |gzip >$DATA_FILE
# myisam引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FILE
C：恢复：
# 通过防火墙禁止web等应用向主库写数据或者锁表。让主库暂时停止更新，然后再进行恢复。
# 误操作删除nick库！
1.检查全备及binlog日志
[root@localhost 3306]# cd /server/backup/
[root@localhost backup]# gzip -d mysql_2015-10-31.sql.gz
[root@localhost backup]# vim mysql_2015-10-31.sql
[root@localhost backup]# grep -i "change" mysql_2015-10-31.sql

2. Aktualisieren und sichern Sie das Binlog sofort

[root@localhost 3306]# mysqladmin -uroot -pnick -S /data/3306/mysql.sock flush-logs
[root@localhost 3306]# cp /data/3306/mysql-bin.000030 /server/backup/
#误操作log-bin,倒数第二
[root@localhost backup]# mysqlbinlog -d nick mysql-bin.000030 >bin.sql  #导出为.sql格式。
[root@localhost backup]# vim bin.sql
找到语句drop database nick删除！！！（误操作语句）

3 >
Das Obige ist der gesamte Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels für jedermann beim Lernen oder bei der Arbeit hilfreich sein kann.

[root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock <mysql_2015-10-31.sql    #恢复之前的数据库全备
[root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql
#恢复删除误操作语言的bin-log。
# 搞定！！！

-->

Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung der Master-Slave-Replikation, Lese-/Schreibtrennung, Sicherung und Wiederherstellung von MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!