Mysql主从复制部署

高木工

修改于 2019-05-06 20:53:00

8170

修改于 2019-05-06 20:53:00

文章被收录于专栏：运维开发运维开发

一. 实验环境

??采用Vagrant搭建mysql集群，这里配置三台mysql服务器，操作系统为Centos7

1.Vagrantfile配置如下：

# -*- mode: ruby -*-
# vi: set ft=ruby :

# 1..3，根据需要修改
Vagrant.configure("2") do |config|
	(1..3).each do |i|
		config.vm.define "mysql#{i}" do |node|
		# 设置虚拟机的Box，根据需要修改
		node.vm.box = "centos7"
		# 设置虚拟机的主机名
		node.vm.hostname="mysql#{i}"
		# 设置虚拟机的IP
		node.vm.network "private_network", ip: "192.168.1.1#{i}"
		# 设置主机与虚拟机的共享目录，根据需要开启
		# config.vm.synced_folder "C:\\ssd", "/ssd"
		# VirtaulBox相关配置
		node.vm.provider "virtualbox" do |v|
			# 设置虚拟机的名称
			v.name = "mysql#{i}"
			# 设置虚拟机的内存大小，根据需要修改
			v.memory = 768
			# 设置虚拟机的CPU个数
			v.cpus = 1
		end
  
		# 使用shell脚本进行软件安装和配置
		node.vm.provision "shell", inline: <<-SHELL
            echo 'do something'
		SHELL
		end
	end
end

2.主机角色如下:

master 192.168.1.11 mysql1
slave 192.168.1.12 mysql2
slave 192.168.1.13 mysql3

3.启动后我们登录mysql1，也就是192.168.1.11，安装mariadb(兼容mysql)，版本为5.5.56

yum install mariadb-server mariadb -y
yum list|grep mariadb

mariadb.x86_64                              1:5.5.56-2.el7             @base    
mariadb-libs.x86_64                         1:5.5.56-2.el7             @base    
mariadb-server.x86_64                       1:5.5.56-2.el7             @base    
...

4.关闭防火墙，清空iptables，避免策略问题给实验带来不必要的干扰

[root@mysql1 vagrant]# iptables -F
[root@mysql1 vagrant]# iptables -X
[root@mysql1 vagrant]# iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         
[root@mysql1 vagrant]#

二. 配置master

1.设置root账户的密码（可选）

# 方法一
mysql_secure_installation

# 方法二
update mysql.user set password=PASSWORD('root') where user='root';

2.登录mysql

# 帐号密码登录（设置过root密码）
mysql -uroot -proot

# 直接登录（没有设置root密码）
mysql

3.授权slave访问master

grant replication slave, replication client on *.* to slave@'%' identified by 'slave';
flush privileges;

4.增加master相关配置

这里，我们参考mariadb安装包自带的配置文件:

[root@mysql1 vagrant]# ls /usr/share/mysql/|grep cnf
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
README.mysql-cnf
[root@mysql1 vagrant]#

根据mariadb的配置建议，我们把master的配置文件单独放到/etc/my.cnf.d/目录下，简单配置如下：

cat > /etc/my.cnf.d/mysql-master.cnf << EOF
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
# required unique id between 2 and 2^32 - 1
server-id= 1
# sync table list
binlog-do-db = test,demodb
# ignore table list
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
EOF

接着，我们重启mariadb并查看master配置效果

[root@mysql1 vagrant]# systemctl restart mariadb
[root@mysql1 vagrant]# systemctl status mariadb
mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled)
...
1月 03 17:56:32 mysql1 mysqld_safe[4511]: 180103 17:56:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
1月 03 17:56:34 mysql1 systemd[1]: Started MariaDB database server.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000006 |      245 | test,demodb  | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

请记住File和Position，这两个字段会在后面slave的配置中用到.

三. 配置slave

从5.5版本的官方文档得知，在slave中不需要配置master相关信息:

https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html

The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options,

you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).

The options affected are shown in this list: --master-host

--master-user

--master-password

--master-port

...

如果配置了反而会出错：

/usr/libexec/mysqld: unknown variable 'master-host=192.168.1.11'

1.这里我们以mysql2的配置为例，首先登录mysql2，然后增加slave配置：

vagrant ssh mysql2

cat > /etc/my.cnf.d/mysql-slave.cnf << EOF
[mysqld]
# 不能和master或者其他slave冲突，这里也可用ip来充当server_id
server-id= 2
EOF

2.创建需要从master复制的db:

create database demodb;

3.登录mysql并为slave指定master信息:

mysql -uroot -proot

# 先停掉工作线程
slave stop

# 方式一：不指定binlog和logpos
# CHANGE MASTER TO MASTER_HOST='192.168.1.11',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='slave';

# 方式二：建议指定binlog和logpos，也就是上文提到的File和Position
CHANGE MASTER TO
  MASTER_HOST='192.168.1.11',
  MASTER_USER='slave',
  MASTER_PASSWORD='slave',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000006',
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;
  
# 启动工作线程
slave start

4.查看slave的状态

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>

重点关注：Slave_IO_Running: Yes和Slave_SQL_Running: Yes说明slave复制线程已经开始工作，此时我们可以看到，指定的db已经同步过来了：

MariaDB [demodb]> show tables;
+---------------------------------+
| Tables_in_demodb                |
+---------------------------------+
...
| django_site                     |
...
| home_application_author         |
| home_application_publisher      |
+---------------------------------+
24 rows in set (0.00 sec)

# 在master中向django_site插入数据，然后到slave中查看很快同步过来

MariaDB [demodb]> select * from django_site;
+----+-------------+-------------+
| id | domain      | name        |
+----+-------------+-------------+
|  1 | example.com | example.com |
|  2 | test        | test        |
|  3 | test1       | test1       |
|  4 | test        | test        |
|  5 | test        | test        |
+----+-------------+-------------+
5 rows in set (0.00 sec)


MariaDB [demodb]> show processlist;
+----+-------------+-----------+--------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db     | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-----------+--------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 11 | root        | localhost | demodb | Query   |    0 | NULL                                                                        | show processlist |    0.000 |
| 12 | system user |           | NULL   | Connect |  391 | Waiting for master to send event                                            | NULL             |    0.000 |
| 13 | system user |           | NULL   | Connect |  391 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
+----+-------------+-----------+--------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)

??两台slave采用相同的方式配置，注意server-id不能重复即可，通过前面的设置，即可实现一个master和多个slave的主从复制功能.

本文系转载，前往查看

如有侵权，请联系?cloudcommunity@tencent.com 删除。

云数据库 SQL Server

本文系转载，前往查看

如有侵权，请联系?cloudcommunity@tencent.com 删除。

云数据库 SQL Server

登录后参与评论

0 条评论

热度