MySQL 的复制有很多种，主从复制，主主复制，一主多从复制等，本文主要演示主从复制。
环境说明
|
1 2 3 |
$ cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) |
安装MySQL
MySQL版本：5.7.xx
|
1 2 3 4 5 6 7 8 9 10 11 |
# 下载 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 进行修改：
|
1 2 3 4 5 6 7 8 |
$ 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，就添加一个允许远程连接的帐户：
|
1 2 |
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; |
MySQL用户相关常用命令：
|
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 |
# 创建用户：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'; |
如果有防火墙记得开放端口
|
1 2 3 |
$ 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密码
然后在两台机上都创建一个用于复制的账号
|
1 2 3 4 5 6 7 |
$ 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库上配置
|
1 2 3 4 5 6 7 8 9 10 |
$ 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目录没有的话，需要自行创建
|
1 2 3 4 |
$ sudo mkdir /var/log/mysql # 分配权限 $ sudo chown mysql:mysql /var/log/mysql |
然后重启Master库的MySQL服务
|
1 2 3 4 5 6 |
$ 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库上配置
|
1 2 3 4 5 6 7 8 9 10 11 |
$ 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 服务
|
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 |
$ 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库服务器
|
1 2 3 4 5 6 7 8 9 10 11 |
# 关闭复制 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库操作
|
1 2 3 4 5 6 7 |
# 登录 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 |
|
1 2 3 4 5 |
# 备份数据库 mysqldump db1 > db1.sql # 解锁 mysql> unlock tables; |
然后在Slave库设置，从已存在的Mater库复制Slave库的配置和上面介绍的基本都一样，只在配置好之后先执行发下操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 将备份上传至从库，并在从库还原数据库 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库的复制参数
|
1 2 3 4 5 6 7 |
# 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库复制也操作完成。
