MySQL配置主从复制

2020年2月25日 0条评论 320次阅读 0人点赞

MySQL 的复制有很多种，主从复制，主主复制，一主多从复制等，本文主要演示主从复制。

环境说明

$ cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)

$ cat /etc/redhat-release

CentOS Linux release 7.5.1804 (Core)

安装MySQL

MySQL版本：5.7.xx

# 下载 mysql 源安装包
$ wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# 安装mysql源
$ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm
# 安装mysql
$ sudo yum install mysql-community-server
# 启动服务
$ sudo systemctl enable mysqld && sudo systemctl start mysqld
# 查看服务状态
sudo systemctl status mysqld

# 下载 mysql 源安装包

$ wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

# 安装mysql源

$ sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm

# 安装mysql

$ sudo yum install mysql-community-server

# 启动服务

$ sudo systemctl enable mysqld && sudo systemctl start mysqld

# 查看服务状态

sudo systemctl status mysqld

MySQL启动后，在 /var/log/mysqld.log 文件中给 root 生成了一个默认密码。通过下面的方式找到 root 默认密码，然后登录 mysql 进行修改：

$ grep 'temporary password' /var/log/mysqld.log
[Note] A temporary password is generated for root@localhost: ********
# 登录 MySQL 并修改密码
$ mysql -u root -p
Enter password:
# 修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

$ grep 'temporary password' /var/log/mysqld.log

[Note] A temporary password is generated for root@localhost: ********

# 登录 MySQL 并修改密码

$ mysql -u root -p

Enter password:

# 修改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

MySQL默认只允许 root 帐户在本地登录，为了安全起见，如果要在其它机器上连接 MySQL，就添加一个允许远程连接的帐户：

mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

1 2	mysql> GRANT ALL PRIVILEGES ON . TO 'dba'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

MySQL用户相关常用命令：

# 创建用户：host指定可以登录的主机，其中localhost表示本机，%表示所有主机
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# 管理用户权限
# privileges：用户的操作权限,如SELECT , INSERT , UPDATE，如果要授予所有权限用all privileges
# databasename：数据库名，指定要授予权限的库，如果要对所有库授予权限用*代替
# tablename：表名，指定要授予权限的表，如果要对所有表授予权限用*代替
# username：被授予权限的用户名
# host：被授予权限的主机，如果要对所有主机授予权限用%代替
mysql> GRANT privileges ON databasename.tablename TO 'username'@'host';

# 撤销用户权限
mysql> REVOKE privileges ON databasename.tablename FROM 'username'@'host';

# 修改用户密码
mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

# 查看权限
# 查看本用户的权限
mysql> SHOW GRANTS;
# 查看指定用户的权限
mysql> SHOW GRANTS;

# 删除用户
# 有一点需要注意：user表中是通过用户名和主机名来唯一确定一个用户，'testuser'@'10.1.1.1'和'testuser'@'10.1.1.2'是两个用户，删除的话要删除两次
mysql> DROP USER 'username'@'host';

# 创建用户：host指定可以登录的主机，其中localhost表示本机，%表示所有主机

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# 管理用户权限

# privileges：用户的操作权限,如SELECT , INSERT , UPDATE，如果要授予所有权限用all privileges

# databasename：数据库名，指定要授予权限的库，如果要对所有库授予权限用*代替

# tablename：表名，指定要授予权限的表，如果要对所有表授予权限用*代替

# username：被授予权限的用户名

# host：被授予权限的主机，如果要对所有主机授予权限用%代替

mysql> GRANT privileges ON databasename.tablename TO 'username'@'host';

# 撤销用户权限

mysql> REVOKE privileges ON databasename.tablename FROM 'username'@'host';

# 修改用户密码

mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

# 查看权限

# 查看本用户的权限

mysql> SHOW GRANTS;

# 查看指定用户的权限

mysql> SHOW GRANTS;

# 删除用户

# 有一点需要注意：user表中是通过用户名和主机名来唯一确定一个用户，'testuser'@'10.1.1.1'和'testuser'@'10.1.1.2'是两个用户，删除的话要删除两次

mysql> DROP USER 'username'@'host';

如果有防火墙记得开放端口

$ sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
$ sudo firewall-cmd --reload

$ sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent

$ sudo firewall-cmd --reload

搭建主从复制

本文介绍一主一备

MySQL复制有两种方式：基于语句的复制和基于行的复制，一般使用基于行的复制。

基于行的复制一般分为 3 步：

在主库上把数据更改记录到二进制日志 (Binary Log) 中；
从库将主库的日志复制到自己的中继日志 (Relay Log) 中；
从库读取中继日志的时间，将其重放到从库的数据库中；

两台 MySQL 服务器

系统	节点IP	节点类型
centos7.5	192.168.3.21	Master
centos7.5	192.168.3.22	Slave

在两台机器上按照上文的说明安装好MySQL，配置好root密码

然后在两台机上都创建一个用于复制的账号

$ mysql -u root -p
Enter password：
# 复制账号一般是不需要REPLICATION CLIENT权限的，这时加上有以下考虑
# 监控和管理复制账号，需要 REPLICATION CLIENT 权限
# 如果发生意外，有时需要从库变为主库，这样做以后会非常方便切换
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replica@'192.168.3.%' IDENTIFIED BY 'password';

$ mysql -u root -p

Enter password：

# 复制账号一般是不需要REPLICATION CLIENT权限的，这时加上有以下考虑

# 监控和管理复制账号，需要 REPLICATION CLIENT 权限

# 如果发生意外，有时需要从库变为主库，这样做以后会非常方便切换

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replica@'192.168.3.%' IDENTIFIED BY 'password';

在master库上配置

$ sudo vim /etc/my.cnf
# 在[mysqld] 这个部分添加如下内容
# log_bin 参数必须唯一, 主库设置为 21 ，从库设置为 22
# binlog_do_db 参数是复制指定的数据库
server_id=21
log_bin=/var/log/mysql/mysql-bin
binlog_do_db=db1
binlog_do_db=db2
binlog_do_db=db3

$ sudo vim /etc/my.cnf

# 在[mysqld] 这个部分添加如下内容

# log_bin 参数必须唯一, 主库设置为 21 ，从库设置为 22

# binlog_do_db 参数是复制指定的数据库

server_id=21

log_bin=/var/log/mysql/mysql-bin

binlog_do_db=db1

binlog_do_db=db2

binlog_do_db=db3

如果log_bin目录没有的话，需要自行创建

$ sudo mkdir /var/log/mysql
# 分配权限
$ sudo chown mysql:mysql /var/log/mysql

$ sudo mkdir /var/log/mysql

# 分配权限

$ sudo chown mysql:mysql /var/log/mysql

然后重启Master库的MySQL服务

$ sudo systemctl restart mysqld
# 登录 MySQL:
$ mysql -u root -p
# 使用 SHOW MASTER STATUS 命令查看
mysql> SHOW MASTER STATUS;

$ sudo systemctl restart mysqld

# 登录 MySQL:

$ mysql -u root -p

# 使用 SHOW MASTER STATUS 命令查看

mysql> SHOW MASTER STATUS;

得到结果

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	12	db1,db2,db3

从结果看到， File 字段有值，并且前面与配置文件一致，说明配置正确。后面的 000001 说明是第一次，如果 MySQL 从启服务，这个值会递增为 mysql-bin.000002

在slave库上配置

$ sudo vim /etc/my.cnf
# 在[mysqld] 这个部分添加如下内容
# log_bin 参数必须唯一, 主库设置为 21 ，从库设置为 22
# binlog_do_db 参数是复制指定的数据库
# 从库使用 read_only=1，这样会将从库设为只读的，如果有其他需求就去掉这个配置选项
# /var/log/mysql 这个目录一个，如果没有也需要创建
server_id=22
log_bin=/var/log/mysql/mysql-bin.log
relay_log=/var/log/mysql/mysql-relay-bin.log
read_only=1

$ sudo vim /etc/my.cnf

# 在[mysqld] 这个部分添加如下内容

# log_bin 参数必须唯一, 主库设置为 21 ，从库设置为 22

# binlog_do_db 参数是复制指定的数据库

# 从库使用 read_only=1，这样会将从库设为只读的，如果有其他需求就去掉这个配置选项

# /var/log/mysql 这个目录一个，如果没有也需要创建

server_id=22

log_bin=/var/log/mysql/mysql-bin.log

relay_log=/var/log/mysql/mysql-relay-bin.log

read_only=1

重启Slave库的 MySQL 服务

$ sudo systemctl restart mysqld
# 登录 MySQL:
$ mysql -u root -p
# 设置复制
# MASTER_LOG_POS 设置为 0，因为要从日志的开头读起
# MASTER_LOG_FILE 设置为 mysql-bin.000001 ，此选项初始化设置时需要跟主库中的一致。设置好后，如果主库发生重启等，不需再次设置，从库会跟着更新
mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.21',
    -> MASTER_USER='replica',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;
# 查看从库状态
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.3.21
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
                               ...
                               ...
                               ...
1 row in set (0.00 sec)
# 从 Slave_IO_State, Slave_IO_Running: No, Slave_SQL_Running: No 表明当前从库的复制服务还没有启动
# 启动复制服务
mysql> START SLAVE;
# 再次查看状态从 Slave_IO_State, Slave_IO_Running, Slave_SQL_Running 的值，可以看出复制已经运行

$ sudo systemctl restart mysqld

# 登录 MySQL:

$ mysql -u root -p

# 设置复制

# MASTER_LOG_POS 设置为 0，因为要从日志的开头读起

# MASTER_LOG_FILE 设置为 mysql-bin.000001 ，此选项初始化设置时需要跟主库中的一致。设置好后，如果主库发生重启等，不需再次设置，从库会跟着更新

mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.21',

-> MASTER_USER='replica',

-> MASTER_PASSWORD='password',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=0;

# 查看从库状态

mysql> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.3.21

Master_User: replica

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

...

1 row in set (0.00 sec)

# 从 Slave_IO_State, Slave_IO_Running: No, Slave_SQL_Running: No 表明当前从库的复制服务还没有启动

# 启动复制服务

mysql> START SLAVE;

# 再次查看状态从 Slave_IO_State, Slave_IO_Running, Slave_SQL_Running 的值，可以看出复制已经运行

如果遇到突发情况，需要将从库改为主库，登录到Slave库服务器

# 关闭复制
mysql> STOP SLAVE;
# 重置，清除复制信息，这样再启动时就不会进行复制了。
mysql> RESET SLAVE ALL;
# 更改从库的 MySQL 配置
$ sudo vim /etc/my.cnf
# 注释掉 read_only 选项，这样使从库变为可读也可写
# read_only=1
# 重启 MySQL 服务
$ sudo systemctl restart mysqld

# 关闭复制

mysql> STOP SLAVE;

# 重置，清除复制信息，这样再启动时就不会进行复制了。

mysql> RESET SLAVE ALL;

# 更改从库的 MySQL 配置

$ sudo vim /etc/my.cnf

# 注释掉 read_only 选项，这样使从库变为可读也可写

# read_only=1

# 重启 MySQL 服务

$ sudo systemctl restart mysqld

从另一个服务器开始复制

一般的情况下是已经有主数据库了，要新建复制数据库，这就需要先使两个 MySQL 实例的内容一致，之后在进行复制配置

首先Master库操作

# 登录 MySQL:
$ mysql -u root -p
# 设置锁
mysql> flush tables with read lock;
# 查看 binlog 的偏移量（注意 Position 字段的值，后面复制需要用到）
mysql> SHOW MASTER STATUS;

# 登录 MySQL:

$ mysql -u root -p

# 设置锁

mysql> flush tables with read lock;

# 查看 binlog 的偏移量（注意 Position 字段的值，后面复制需要用到）

mysql> SHOW MASTER STATUS;

得到结果

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	184

# 备份数据库
mysqldump db1 > db1.sql
# 解锁
mysql> unlock tables;

# 备份数据库

mysqldump db1 > db1.sql

# 解锁

mysql> unlock tables;

然后在Slave库设置，从已存在的Mater库复制Slave库的配置和上面介绍的基本都一样，只在配置好之后先执行发下操作

# 将备份上传至从库，并在从库还原数据库
mysql> source db1.sql;
# 如果主库的表有使用 blob 等存储二进制数据类型的字段，需要设置从库的 max_allowed_packet 参数，之后再导入数据库。
$ sudo vim /etc/my.cnf
# 添加以下内容，各字段的值请根据实际情况修改
max_allowed_packet=100M
net_buffer_length=8K
bulk_insert_buffer_size=128M
# 备份数据库
mysqldump db1 > db1.sql
# 解锁
mysql> unlock tables;

# 将备份上传至从库，并在从库还原数据库

mysql> source db1.sql;

# 如果主库的表有使用 blob 等存储二进制数据类型的字段，需要设置从库的 max_allowed_packet 参数，之后再导入数据库。

$ sudo vim /etc/my.cnf

# 添加以下内容，各字段的值请根据实际情况修改

max_allowed_packet=100M

net_buffer_length=8K

bulk_insert_buffer_size=128M

# 备份数据库

mysqldump db1 > db1.sql

# 解锁

mysql> unlock tables;

然后设置Slave库的复制参数

# MASTER_LOG_POS的值要和Master库的Position值保持一致
mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.21',
    -> MASTER_USER='replica',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

# MASTER_LOG_POS的值要和Master库的Position值保持一致

mysql> CHANGE MASTER TO MASTER_HOST='192.168.3.21',

-> MASTER_USER='replica',

-> MASTER_PASSWORD='password',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=0;

至此，从已存在的Mater库复制也操作完成。