MySQL 5.7.22 主从复制--use

一、mysql主从配置

1、解决web应用系统，数据库出现的性能瓶颈，采用数据库集群的方式来实现查询负载；
2、mysql支持数据库的主从复制功能，使用主数据库进行数据的写入操作，从数据库则用来进行数据读操作

二、数据库配置

1、修改主库的配置文件(my.cnf)

[root@VMTest ~]#  vi /etc/my.cnf

[client]
default-character-set=utf8   #设置mysql客户端默认字符集

[mysqld]
datadir=/data/mysql     #设置mysql数据库的数据的存放目录
socket=/var/lib/mysql/mysql.sock

character-set-server = utf8
collation-server = utf8-general-ci

binlog-cache-size = 524288
expire-logs-days = 15

# ------------------------ log 设置 ------------------------- #
slow-query-log = 1  #慢查询
slow-query-log-file = /data/logs/mysql/mysql-slow.log

log-slow-slave-statements = 1
long-query-time = 8
min-examined-row-limit = 100

# ------------------------ 主从复制 master设置 ------------------------- #
log-bin = /data/logs/mysql/mysql-bin.log   #开启二进制日志
log-bin-index = /data/logs/mysql/mysql-bin.index
max-binlog-size = 1G
server-id = 101

binlog-format=mixed
binlog-do-db=opportmgt   # 只记录指定库的更新到binlog
binlog-ignore-db=mysql   #不记录指定库的更新到binlog
binlog-ignore-db=information-schema
binlog-ignore-db=performance-schema

# ----------------------------------------------------- #
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2、修改从库的配置文件(my.cnf)

[root@VMTest ~]#  vi /etc/my.cnf

[client]
default-character-set=utf8   #设置mysql客户端默认字符集

[mysqld]
datadir=/data/mysql     #设置mysql数据库的数据的存放目录
socket=/var/lib/mysql/mysql.sock

character-set-server = utf8
collation-server = utf8-general-ci

binlog-cache-size = 524288
expire-logs-days = 15

# ------------------------ log 设置 ------------------------- #
slow-query-log = 1  #慢查询
slow-query-log-file = /data/logs/mysql/mysql-slow.log

log-slow-slave-statements = 1
long-query-time = 8
min-examined-row-limit = 100

# ------------------------ 主从复制 slave设置 ------------------------- #
server-id = 102

relay-log       = /data/logs/mysql/slave-relay-bin
relay-log-index = /data/logs/mysql/slave-relay-bin.index


log-bin = /data/logs/mysql/mysql-bin.log   #开启二进制日志
log-bin-index = /data/logs/mysql/mysql-bin.index
max-binlog-size = 1G

symbolic-links=0

binlog-format=mixed
binlog-do-db=opportmgt   # 只记录指定库的更新到binlog


# ----------------------------------------------------- #
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

三、关联主库与从库

1、 在主库（master）中创建一个用户用于与从库同步的用户名和密码（这里我创建一个slave_user用户，密码为123456），并给slave_user用户授权，以用于主库操作从库

1.master创建授权用户:

在主服务器新建一个用户赋予“REPLICATION SLAVE”的权限, 不需要再赋予其它的权限。
在下面的命令，把X.X.X.X替换为从服务器的IP。
    a. mysql>CREATE USER 'user'@ 'X.X.X.X' IDENTIFIED BY 'password';
    b. mysql>GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';
例如:
    192.168.78.101(主库master), 192.168.1.102(从库slave):
    在主库上设置: 
    mysql> create user 'mstr'@'192.168.1.102' identified by 'MyPass1!';
    mysql> grant replication slave on *.* to 'mstr'@'192.168.1.102';
    mysql> flush privileges; 
 
## 查看用户
mysql> select user,host from mysql.user;
 
## 查看 master 状态
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 182685240 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
注:这里的 mysql-bin.000002和 Position 值 slave 配置时需要用到。
 
-- mysql>UNLOCK TABLES; # 解锁数据表

2.将 master 中现有的数据信息导出:
$ mysqldump -u root -p --all-databases --master-data > all.sql
 
3.将 all.sql 发送到 slave 服务器 tmp 目录下:
$ scp all.sql root@192.168.78.130:/tmp
 
4.slave 导入 master 数据, 使 master-slave 数据保持一致:
192.168.78.130(slave):
$ mysql –u root -p < all.sql
 
5.在从数据库上, 使 slave 与 master 建立连接, 从而同步（在从服务器上操作）:
// slave> reset slave all   #重新change master to

mysql> change master to
    -> master_host='192.168.1.101',
    -> master_user='test1',
    -> master_password='MyPass1!',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=182685240,
    -> MASTER_CONNECT_RETRY=10;

    #关于--master-connect-retry=seconds:
    在主服务器宕机或连接丢失的情况下, 从服务器线程重新尝试连接主服务器之前睡眠的秒数。
    如果主服务器.info文件中的值可以读取则优先使用. 如果未设置, 默认值为60。

mysql> start slave;               #开启主从同步; 关闭: mysql> stopt slave;
mysql> show slave status \G       #查看 slave的状态
 
 
注:
l master_log_file 和 master_log_pos值为主库上面执行show master status得到
2 如果 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes, 说明配置成功
3 如果其中一项不为 Yes, 查看 Last_IO_Errno 错误码和错误信息, 或者查看 MySQL 日志信息并查找对应问题
命令:
    mysql slave 从库的log 清理
    1. mysql> stop slave
    2. mysql> reset slave
    3. mysql> start slave

四、关联主库与从库

在从库中建立只读用户，避免往从库中写数据。

master 插入一条数据，slave查看是否成功