环境：
192.168.205.17: as master server
192.168.205.27: as slave server
192.168.205.37: as slave server
版本：
OS: centos 7 1810 with mini install
mariadb-5.5.60
目地：
当数据库运行了一段时间后，如何再做主从复制,并当主服务器down机时，如何提升从为主
步骤：
1. 配置主服务器
2. 备份主服务器
3. 配置从服务器
4. 将备份恢复从服务器
5. 测试主从复制
6. 模拟主服务器down机，提升从节点
7. 测试主服务器192.168.205.17
- 首先安装MariaDB
[root@Master ~]#yum install mariadb-server - 分创建数据目录和logs目录
[root@Master ~]#mkdir /data/{mysql,logs} [root@Master ~]#chown -R mysql:mysql /data/{mysql,logs} - 修改配置文件
[root@Master ~]#vi /etc/my.cnf [mysqld] server_id=17 #服务器ID必须唯一 datadir=/data/mysql #数据文件路径 log_bin=/data/logs/bin #日志文件路径 [root@Master ~]#systemctl restart mariadb - 建立复制帐号，此帐号是从服务器与主服务器同步时连接用的帐号，因为对所有数据库同步，所以是.
MariaDB [(none)]> GRANT replication slave on *.* to repluser@'192.168.205.%' identified by 'centos'; - 备份数据库,因为你的数据库运行了一段时间，为了把以前的数据也要同步过去，所以先备份主的服务器数据，再恢复到从的节点上，其中--master-data=1表示是从从节点上恢复数据，在备份的sql中会启用change master to语句
[root@Master ~]#mysqldump -A --single-transaction --master-data=1 -F >/data/all.mysql - 将备份的文件复制到slave服务器上
[root@Master ~]#scp /data/all.mysql 192.168.205.27:/data/从服务器192.168.205.27
- 安装MariaDB在slave服务器上
[root@slave ~]#yum install mariadb-server - 修改配置文件
[root@slave ~]#vi /etc/my.cnf [mysqld] datadir=/data/mysql read-only #只给slave数据只读权限，当然只能限制普通帐号 log-bin=/data/logs/bin server-id=27 #修改server-id一样和主不一样才行 - 创建数据和日志文件夹并更改所有者和所有组为mysql
[root@slave ~]#mkdir /data/{mysql,logs} [root@slave ~]#chown mysql:mysql /data/{mysql,logs} - 在slave服务器中打开备份的文件，添加如下的内容
[root@salve data]#vi all.mysql CHANGE MASTER TO MASTER_HOST='192.168.205.17', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_LOG_FILE='bin.000005', MASTER_LOG_POS=245; - 启动服务
[root@slave ~]#systemctl restart mariadb - 直接恢恢复数据库
[root@slave ~]#mysql < /data/all.mysql - 连接mariaDB并查看状态
[root@slave ~]#mysql MariaDB [(none)]> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 27 | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show slave status\G ... Slave_IO_Running: No Slave_SQL_Running: No ... - 起动slave I/O thread 和slave SQL thread线程, 并查看状态
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000005 Read_Master_Log_Pos: 402 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes - 查看数据库是否同步过来
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 6 rows in set (0.00 sec) - 查看网络连接，已经连接到主服务器的3306
[root@slave data]#ss -nt State Recv-Q Send-Q Local Address:Port Peer Address:Port ESTAB 0 96 192.168.205.27:22 192.168.205.1:17526 ESTAB 0 0 192.168.205.27:56360 192.168.205.17:3306测试
- 在主的服务器上，建立一个数据库，并测试数据库同步
MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.00 sec) - 在从服务器上看有没有数据库db1
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 7 rows in set (0.00 sec) - 如果在主和从服务器上同时创建了一个数据库db2,会引起线程停止，复制也会停止
主服务器：MariaDB [(none)]> create database db2; Query OK, 1 row affected (0.00 sec)从服务器：
MariaDB [(none)]> create database db2; Query OK, 1 row affected (0.00 sec)此时在从服务器上查看状态：
MariaDB [(none)]> show slave status\G ... Slave_IO_Running: Yes Slave_SQL_Running: No Last_SQL_Errno: 1007 Last_Error: Error 'Can't create database 'db2'; database exists' on query. Default database: 'db2'. Query: 'create database db2' ...此时在主服务器中再建立数据库db3：
MariaDB [(none)]> create database db3; Query OK, 1 row affected (0.00 sec)再从服务器上看没有同步：
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 8 rows in set (0.00 sec) - 我们可以在从服务器上删除数据库db2，来解决同步冲突的问题，但错误还在
MariaDB [hellodb]> drop database db2; Query OK, 0 rows affected (0.00 sec) -
我们必须要重启slave进程才能继续复制
从服务器上重启线程：MariaDB [hellodb]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 9 rows in set (0.00 sec) -
同样也们也可以对表进行操作，在主和从的相同的库和表中插入一个条主键相同记录
从服务器MariaDB [(none)]> use hellodb; MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec) MariaDB [hellodb]> desc teachers; +--------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | TID | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(100) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | | Gender | enum('F','M') | YES | | NULL | | +--------+----------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> insert teachers (name,age) values('leo',33); Query OK, 1 row affected (0.00 sec) MariaDB [hellodb]> select *from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | leo | 33 | NULL | +-----+---------------+-----+--------+ 5 rows in set (0.00 sec)在主服务器上同样的插入一条记录：
MariaDB [hellodb]> insert teachers (name,age) values('ivan',38); Query OK, 1 row affected (0.00 sec)在从服务器上可以看到1062同步错误：
MariaDB [hellodb]> show slave status\G ... Slave_IO_Running: Yes Slave_SQL_Running: No Last_Errno: 1062 Last_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'hellodb'. Query: 'insert teachers (name,age) values('ivan',38)' ... -
以上问题我们可以使用忽略同步的错误，继续同步，然后再找出错误的原因
在从服务器上：MariaDB [hellodb]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> set global sql_slave_skip_counter = 1; #数字代表忽略几个错误 Query OK, 0 rows affected (0.00 sec) MariaDB [hellodb]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000005 Read_Master_Log_Pos: 881 Relay_Log_File: mariadb-relay-bin.000005 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... - 或者把错误编号添加到配置文件中：
[root@slave ~]#vi /etc/my.cnf [mysqld] skip_slave_errors = 1062,1077 #可以使用错误编号，多个用逗号,分开， [root@slave ~]#systemctl restart mariadb多个从节点的复制，并模拟主节点down机
- 我们再加一下从节点slave2: 192.168.205.37
[root@slave2 ~]#yum install mariadb-server [root@slave2 ~]#vi /etc/my.cnf [mysqld] server-id=37 datadir=/data/mysql log-bin=/data/logs/bin read-only [root@slave2 ~]#mkdir /data/{mysql,logs} [root@slave2 ~]#chown mysql:mysql /data/{mysql,logs} [root@slave2 ~]#systemctl start mariadb - 在主节点上完全备份数据库
[root@Master ~]#mysqldump -A --single-transaction --master-data=1 -F >/data/all2.mysql [root@Master ~]#scp /data/all2.mysql 192.168.205.37:/data - 在第二个从节点上修改配置文件,并启动线程
[root@slave2 ~]#vi /data/all2.mysql ... CHANGE MASTER TO MASTER_HOST='192.168.205.17', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_LOG_FILE='bin.000006', MASTER_LOG_POS=245; ... MariaDB [zhaoli]> source /data/all2.mysql MariaDB [zhaoli]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000006 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... -
主服务器上执行如下的一个存储过程, 模拟数据正在更新
use test create table test (id int auto_increment primary key,name char(10)); delimiter $$ create procedure proc_test() begin declare i int; set i = 1; while i < 100000 do insert into test(name) values (concat('zhao',i)); set i = i +1; end while; end$$ delimiter ; MariaDB [test]> call proc_test; - 模拟主服务器故障（你可以关机呀），查看所有的从服务器，找一个最新的日志服务提为主。
slave服务器:MariaDB [test]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Reconnecting after a failed master event read Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000006 #复制的日志文件 Read_Master_Log_Pos: 8981180 #复制的日志位置 Relay_Log_File: mariadb-relay-bin.000009 Relay_Log_Pos: 8981458 Relay_Master_Log_File: bin.000006 Slave_IO_Running: Connecting Slave_SQL_Running: Yesslave2服务器
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Reconnecting after a failed master event read Master_Host: 192.168.205.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000006 Read_Master_Log_Pos: 8981180 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 8981458 Relay_Master_Log_File: bin.000006 Slave_IO_Running: Connecting Slave_SQL_Running: Yes - 这里我们把slave: 192.168.205.27提升为主节点
先停掉slave线程：MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec)看一下目录结构：
[root@slave data]#ll /data/mysql/ total 37516 -rw-rw---- 1 mysql mysql 16384 Aug 5 22:10 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Aug 5 22:10 aria_log_control drwx------ 2 mysql mysql 4096 Aug 5 20:10 db1 drwx------ 2 mysql mysql 4096 Aug 5 21:51 db2 drwx------ 2 mysql mysql 4096 Aug 5 21:51 db3 drwx------ 2 mysql mysql 4096 Aug 5 22:34 db4 drwx------ 2 mysql mysql 4096 Aug 5 19:39 hellodb -rw-rw---- 1 mysql mysql 18874368 Aug 5 22:37 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Aug 5 22:37 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Aug 5 22:37 ib_logfile1 -rw-rw---- 1 mysql mysql 333 Aug 5 22:24 mariadb-relay-bin.000008 -rw-rw---- 1 mysql mysql 8981458 Aug 5 22:37 mariadb-relay-bin.000009 -rw-rw---- 1 mysql mysql 54 Aug 5 22:24 mariadb-relay-bin.index -rw-rw---- 1 mysql mysql 82 Aug 5 22:47 master.info drwx------ 2 mysql mysql 4096 Aug 5 19:39 mysql drwx------ 2 mysql mysql 4096 Aug 5 18:25 performance_schema -rw-rw---- 1 mysql mysql 54 Aug 5 22:47 relay-log.info drwx------ 2 mysql mysql 4096 Aug 5 22:36 test drwx------ 2 mysql mysql 4096 Aug 5 19:39 zhaoli清除slave信息：
MariaDB [(none)]> reset slave; Query OK, 0 rows affected (0.01 sec)查看一下目录结构，发现少了master.info, relay-log.info, 并ralaylog重新开始新的:
[root@slave data]#ll /data/mysql/ total 28736 -rw-rw---- 1 mysql mysql 16384 Aug 5 22:10 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Aug 5 22:10 aria_log_control drwx------ 2 mysql mysql 4096 Aug 5 20:10 db1 drwx------ 2 mysql mysql 4096 Aug 5 21:51 db2 drwx------ 2 mysql mysql 4096 Aug 5 21:51 db3 drwx------ 2 mysql mysql 4096 Aug 5 22:34 db4 drwx------ 2 mysql mysql 4096 Aug 5 19:39 hellodb -rw-rw---- 1 mysql mysql 18874368 Aug 5 22:37 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Aug 5 22:37 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Aug 5 22:37 ib_logfile1 -rw-rw---- 1 mysql mysql 264 Aug 5 22:47 mariadb-relay-bin.000001 -rw-rw---- 1 mysql mysql 27 Aug 5 22:47 mariadb-relay-bin.index drwx------ 2 mysql mysql 4096 Aug 5 19:39 mysql drwx------ 2 mysql mysql 4096 Aug 5 18:25 performance_schema drwx------ 2 mysql mysql 4096 Aug 5 22:36 test drwx------ 2 mysql mysql 4096 Aug 5 19:39 zhaoli全部清除包括同步的信息，所有同步信息都丢失：
MariaDB [(none)]> reset slave all; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G Empty set (0.00 sec) - 修改slave:192.168.205.27配置文件符合为主的配置,不能为只读，且必须启用二进制日志
[root@slave data]#vi /etc/my.cnf [mysqld] datadir=/data/mysql log-bin=/data/logs/bin #read-only server-id=27 [root@slave data]#systemctl restart mariadb - slve: 192.168.205.27,此时的帐号由于之前备份前创建，所以帐号不需要重建，否则你需要重新建立帐号：
MariaDB [mysql]> select user,host,password from user; +----------+---------------------+-------------------------------------------+ | user | host | password | +----------+---------------------+-------------------------------------------+ | repluser | 192.168.205.% | *128977E278358FF80A246B5046F51043A2B1FCED | +----------+---------------------+-------------------------------------------+ 7 rows in set (0.00 sec)确定从那个地方进行复制：
MariaDB [mysql]> show master logs; +------------+-----------+ | Log_name | File_size | +------------+-----------+ | bin.000001 | 30373 | | bin.000002 | 1038814 | | bin.000003 | 264 | | bin.000004 | 522418 | | bin.000005 | 264 | | bin.000006 | 245 | +------------+-----------+ 6 rows in set (0.00 sec) -
修改slave2: 192.168.205.37 的同步信息,修改前一定要先停止slave
MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> reset slave all; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.205.27', #此处为slave IP 192.168.205.27 -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_LOG_FILE='bin.000006', #日志同上show master logs相同 -> MASTER_LOG_POS=245; #起始位置与show master logs相同 Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.205.27 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000006 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 523 Relay_Master_Log_File: bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... -
在新主服务器上slave:192.168.205.27删除一个库文件，测试同步情况
MariaDB [mysql]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 10 rows in set (0.00 sec) MariaDB [mysql]> drop database db4; Query OK, 0 rows affected (0.00 sec) - 在新从服务器slave2:192.168.205.37上查看，db4已经被删
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | hellodb | | mysql | | performance_schema | | test | | zhaoli | +--------------------+ 9 rows in set (0.00 sec)
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
