mysql主从复制

Posted on | | Visitors:

环境

OS：Centos 7.3

DB version： mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

host1(master): 172.16.143.171

host2(slave): 172.16.143.172

安装

两个节点都执行下面的步骤

1
2
3
yum install mariadb-server -y

systemctl start mariadb

执行db安全选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

master节点配置

在/etc/my.cnf文件的[mysqld]中添加如下内容

1
2
3
4
5
server-id=1
log-bin = /var/lib/mysql/mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

重启db

1
systemctl restart mariadb
1
2
3
4
5
6
7
8
9
mysql -u root -p

GRANT REPLICATION SLAVE ON *.* TO 'replic_user'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

SHOW MASTER STATUS;

QUIT;

如果 master 中已经存在需要复制的数据，则需要dump出来，然后在slave上重放

1
2
3
4
5
mysql -u root -p

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;
1
2
3
4
mysqldump -u root -p --databases [database-1] [database-2] ...  > /root/db_dump.sql

mysql -u root -p
mysql> UNLOCK TABLES;

slave节点配置

如果master节点上dump了数据，则需要执行下面的步骤进行重放。

1
2
scp root@172.16.143.171:/root/db_dump.sql /root/db_dump.sql
mysql -u root -p < /root/db_dump.sql

在/etc/my.cnf文件的[mysqld]中添加如下内容

1
2
3
4
5
server-id=2
og-bin = /var/lib/mysql/mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

重启db

1
systemctl restart mariadb
1
2
3
mysql -u root -p

mysql> CHANGE MASTER TO MASTER_HOST='172.16.143.171',MASTER_USER='replic_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

上面命令中MASTER_LOG_FILE 和 MASTER_LOG_POS 需要根据master节点中

master status```命令结果调整。
1
2
3
4
5

```bash
START SLAVE;

SHOW SLAVE STATUS\G

测试

在master节点中创建db，也可以在slave节点中看到。

双向复制

上面的配置实现了host1 –> host2 的复制关系，如需要实现双向复制，只需要按照上面的步骤再配置从 host2 –> host1 的复制关机即可。

另外在host1的/ect/my.cnf中添加如下内容

1
2
auto-increment-increment = 2
auto-increment-offset = 1

在host2的/ect/my.cnf中添加如下内容

1
2
auto-increment-increment = 2
auto-increment-offset = 2