mysql主从数据库配置

原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态
过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
优点：作为备用数据库，并且不影响业务。可做读写分离，一般是一个写库，一个或多个读库，分布在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性
————————————————

参考：https://blog.youkuaiyun.com/weixin_44393420/article/details/110722753

1、主库

• 修改my.cnf文件，在[mysqld]加入下面的内容：

# 服务的唯一编号
server-id = 1

# 开启mysql binlog功能
log-bin = mysql-bin

# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW

# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal

# 指定需要复制的数据库名为jgyw
binlog-do-db = furniture_store

• 修改好配置文件，重启mysql服务

systemctl restart mysql.service

• 创建从库同步数据的账号：这两个命令是在mysql的终端执行的。

grant replication slave on *.* to 'jgyw'@'192.168.197.136' identified by 'j123456';
flush privileges;

 mysql8以上版本使用下面方法：

在Mysql 8版本中，分两步设置权限 

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password'WITH GRANT OPTION;
这一行命令来设置用户权限，必须分两步来实现设置用户权限【先创建用户、在对该用户分配用户权限】，最后刷新权限

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

• 查看主库的状态：mysql的终端执行

show master status\G;

返回的信息为：

*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 2380
     Binlog_Do_DB: jgyw
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

2、配置从库

• 修改my.cnf文件，在[mysqld]加入下面的内容：

# 服务的唯一编号
server-id = 2

# 开启mysql binlog功能
log-bin = mysql-bin

# binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW

# 减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal

# 指定需要复制的数据库名为furniture_store
replicate-do-db = furniture_store

• 修改好配置文件，重启mysql服务

systemctl restart mysql.service

• 执行同步命令：mysql的终端执行

# 第一步：设置主服务器ip，同步账号密码，同步位置
change master to master_host='192.168.197.135',master_user='jgyw',master_password='jgyw@123',master_log_file='mysql-bin.000002',master_log_pos=2380;

#######注意：########## mysql8以上：get_master_public_key=1
错误提示：ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

【分析】mysql8默认使用插件caching_sha2_password,有些client连接报这个错误，需要拿到server的public key来加密password。

【解决】加参数可以解决：--get-server-public-key
master_host='192.168.197.135',master_user='jgyw',master_password='jgyw@123',master_log_file='mysql-bin.000002',master_log_pos=2380,get_master_public_key=1;

# 第二步：开启同步功能
start slave;

• 查看从库的状态

  mysql的终端执行:

  show slave status\G;
  

  返回信息为：

注意：Slave_IO_Running和Slave_SQL_Running的状态都为Yes时，说明从库配置成功。

可能碰到的问题

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

mysql 5.6 之后引入了uuid的概念，各个复制主从结构中的 server_uuid 要保证不一样。由于我这台新 Mac 是用系统自带的「迁移助理」从老机器迁移过来的，所以说，所有的软件和配置都是一模一样的。

通过下面的语句可查看 server_uuid 的值 和 auto.cnf 的路径，auto.cnf 中保存了 server_uuid 的值。

show variables like '%server_uuid%';
show variables like '%datadir%';

我查到的从服务器上的 datadir 目录在 /usr/local/var/mysql，修改目录下的 auto.cnf 文件中的 server-uuid 的值

修改 datadir 目录下的auto.cnf 文件中的 server-uuid 的值。

然后再重启服务，查看同步状态就不会有这个问题了。

3、从数据库数据初始化：跨服务器数据导入

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
 
mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test 

注意：先在从数据库停止同步，stop slave;数据倒过来后start slave;

参考：Mysql主库有数据，从库从新初始化数据流程_Hoboson0316的博客-优快云博客
=======================================================================Mysql主库有数据，从库从新初始化数据流程============================================================================
0、停止应用，mysql主库全库备份
1、停止从库复制进程
    stop slave ;
2、锁定主数据库，只允许读取不允许写入，这样做的目的是防止备份过程中或备份完成之后有新数据插入，导致备份数据和主数据数据不一致。主库锁表（将所有的脏页都要刷新到磁盘，然后对所有的表加上了读锁）
    flush tables with read lock;
3、查询主数据库状态，并记下FILE及Position的值
    show master status;
4、备份主库数据
    mysqldump  aqzx           -usystem -p'XXX'  >    aqzx.sql     
    mysqldump  yhzx           -usystem -p'XXX'  >    yhzx.sql 
    mysqldump  confcenter     -usystem -p'XXX'  >    confcenter.sql 
    mysqldump  lhywzx         -usystem -p'XXX'  >    lhywzx.sql 
    mysqldump  aqzx           -usystem -p'XXX'  >    aqzx.sql     
5、将主库数据导入从库数据库 保持主从数据一致
    drop database aqzx;
    create database aqzx;  
    mysql  aqzx        -usystem -p'XXX'  <   aqzx.sql          
6、从库配置
    change master to master_host='172.17.0.2',
     master_user='slave',
     master_password='XXX',
     master_port=3306,
     master_log_file='mysql-bin.XXXX',
     master_log_pos= 4952,
     master_connect_retry=30;  
7、启动主从复制start slave;，此时查看从库状态show slave status \G;，若是SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明开启主从复制过程成功。
8、解锁主数据库 切换回主数据库的终端，进行表解锁操作。
    unlock tables;
9、测试主从复制
总结：是对主库的数据进行备份，然后将主数据库中导出的数据导入到从数据库，然后再开启主从复制，以此来保证主从数据库数据一致。