LINUX主物理机+从虚拟机下MYSQL8负载均衡之主从复制亲测可用

这里用的大版是MYSQL8，有些个别语法以实战为准

先在局域网中进行测试

主：192.168.1.194

从：192.168.1.164

主数据中打开/etc/my.cnf

[mysqld]
server-id       = 1（这个原来下面有），如果是虚拟机克隆过去的注意这里，改成2也不行，后来才发现下面有

可能的错误是

 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

当时用的是set global

mysql> set global server_id=2;

stop/start slave;（有网友反着写的可能是不同版本的吧）

改后是mysql> show variables like '%server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.01 sec)

UUID可能也会一样是虚拟机上克隆过去的，可能我这里194改成物理机后自动变了，记不清了

改的话在[/www/server/data/]auto.cnf

164，下面是194上的

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 9cdecb71-b199-11ec-9e78-b083fe6bb807 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| character_set_server            | utf8mb4                              |
| collation_server                | utf8mb4_general_ci                   |
| immediate_server_version        | 999999                               |
| innodb_dedicated_server         | OFF                                  |
| innodb_ft_server_stopword_table |                                      |
| original_server_version         | 999999                               |
| server_id                       | 1                                    |
| server_id_bits                  | 32                                   |
| server_uuid                     | 62d87af7-9880-11ec-a2e0-b083fe6bb807 |
+---------------------------------+--------------------------------------+

主数据库配置：

# log config
log-bin = mysql-bin
# log config
log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件
中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
#
auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误

 这段网友提供，写得很清楚直接引用

从数据库配置：

server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

主数据库中添加测试从帐户

MYSQL8
grant all privileges on *.*  to  'repltest164'@'192.168.1.164'  identified by 'xxx'  with grant option;

mysql> Grant all privileges on repltest164.* to 'repltest164'@'192.168.1.164';
Query OK, 0 rows affected (0.05 sec)
RIGHT;

mysql> Grant all privileges on repltest164.* to 'repltest164'@'192.168.1.164';

mysql> select host,user from user;
+---------------+------------------+
| host          | user             |
+---------------+------------------+
| 192.168.1.%   | root             |
| 192.168.1.164 | repltest164      |
| localhost     | mysql.infoschema |
| localhost     | mysql.session    |
| localhost     | mysql.sys        |
+---------------+------------------+
5 rows in set (0.00 sec)
flush privileges;

关键一步：

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.194',  
MASTER_USER = 'repltest164', 
MASTER_PASSWORD = '123456',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'mysql-bin.000011',
MASTER_LOG_POS = 156,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;

基中master_log_file在show master status中找：

 

grant replication slave on *.* to 'repl_test164'@'192.168.1.164';

记得重启数据库：

service mysql restart;

mysql -urepltest164 -h 192.168.1.164 -p登录

show slave status \G;

这里面有错在配置中找指定的日志目录

不知道主机名用hostname命令获得 <hostname>
再找find / -name <hostname>.err 

 以下可能如下等若干问题，看日志提示逐个解决

The service command supports only basic LSB actions (start, stop, restart, try-restart, reload, force-reload, status). For other actions, please try to use systemctl.

测试效果在主194上新建XXX数据库，164从是也跟着建了一个

 

 注意从表如查不是readonly，测试先删从库的XXX会假死不报错。而如果主从策略如上，而不是跳过错误，其它的主再有改变也会卡在这里，日志里也会报错，得再在从库中建个XXX让它自动删掉才能正常，即先恢复原样让自动同步起作用后再进行后续操作，表操作也一样。