MySQL8.0的主从配置过程记录

Last login: Wed Feb 27 01:17:42 2019 from 172.16.5.80
[root@vmhzpmysql ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

[root@vmhzpmysql ~]# cat /etc/hostname
vmhzpmysql
[root@vmhzpmysql ~]# vim /etc/hostname
vmhzpmysql
"/etc/hostname" 1L, 13C written

[root@vmhzpmysql ~]# reboot

Last login: Wed Feb 27 01:18:27 2019 from 172.16.5.80
[root@vmhzpmysql ~]# cat /etc/hostname
MySQL-Master

[root@MySQL-Master ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2019-02-27 01:20:35 CST; 29s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5034 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5081 (mysqld)
Status: "SERVER_OPERATING"
CGroup: /system.slice/mysqld.service
└─5081 /usr/sbin/mysqld

Feb 27 01:20:33 MySQL-Master systemd[1]: Starting MySQL Server...
Feb 27 01:20:35 MySQL-Master systemd[1]: Started MySQL Server.

[root@MySQL-Master ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

[root@MySQL-Master ~]# vim /etc/my.cnf
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
pid-file=/var/run/mysqld/mysqld.pid
lob-bin=mysql-bin
server-id=1
"/etc/my.cnf" 33L, 1273C written

[root@MySQL-Master ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> CREATE USER 'mysqlslave'@'172.16.1.217' IDENTIFIED WITH mysql_native_password BY '1qaz!QAZ';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysqlslave'@'172.16.1.217';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,plugin,authentication_string from user;
+------------------+--------------+-----------------------+------------------------------------------------------------------------+
| user | host | plugin | authentication_string |
+------------------+--------------+-----------------------+------------------------------------------------------------------------+
| root | % | caching_sha2_password | $A$005$]O}TWbp~#~yS.EIX2r98xFuDwQbfMCcDy7uU9i7fuIEtm0YCG2qweQ59. |
| mysqlslave | 172.16.1.217 | mysql_native_password | *4FD1171C5972B6C561CEE417DA4AD6D6E87C9C5C |
| mysql.infoschema | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+------------------+--------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> alter user 'root'@'%' identified with mysql_native_password by '1qaz!QAZ';
Query OK, 0 rows affected (0.00 sec)

==============================================以上是主MySQL的设置====================================================
==============================================以下是主MySQL的设置====================================================
Last login: Wed Feb 27 00:56:47 2019 from 172.16.5.80
[root@vmhzpmysql ~]# vim /etc/hostname
vmhzpmysql
"/etc/hostname" 1L, 12C written
[root@vmhzpmysql ~]# cat /etc/hostname
MySQL-Slave
[root@vmhzpmysql ~]# reboot

Last login: Wed Feb 27 01:21:10 2019 from 172.16.5.80
[root@MySQL-Slave ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2019-02-27 01:22:32 CST; 19s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5039 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5083 (mysqld)
Status: "SERVER_OPERATING"
CGroup: /system.slice/mysqld.service
└─5083 /usr/sbin/mysqld

Feb 27 01:22:30 MySQL-Slave systemd[1]: Starting MySQL Server...
Feb 27 01:22:32 MySQL-Slave systemd[1]: Started MySQL Server.
Last login: Wed Feb 27 01:22:33 2019 from 172.16.5.80
Last login: Wed Feb 27 01:23:38 2019 from 172.16.5.80

[root@MySQL-Slave ~]# vim /etc/my.cnf
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
pid-file=/var/run/mysqld/mysqld.pid
"/etc/my.cnf" 32L, 1255C written

[root@MySQL-Slave ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2

[root@MySQL-Slave ~]# mysql- u root -p
-bash: mysql-: command not found
[root@MySQL-Slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> CHANGE MASTER TO
-> MASTER_HOST='172.16.1.216',
-> MASTER_USER='mysqlslave',
-> MASTER_PASSWORD='1qaz!QAZ',
-> MASTER_LOG_FILE='binlog.000011',
-> MASTER_LOG_POS=859;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.1.216
Master_User: mysqlslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000011
Read_Master_Log_Pos: 859
Relay_Log_File: MySQL-Slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000011
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 859
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.1.216
Master_User: mysqlslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000011
Read_Master_Log_Pos: 859
Relay_Log_File: MySQL-Slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000011
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 859
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13117
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_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190227 01:38:32
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> set global server_id=2;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.1.216
Master_User: mysqlslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000011
Read_Master_Log_Pos: 859
Relay_Log_File: MySQL-Slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000011
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 859
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13117
Last_IO_Error: 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.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190227 01:40:32
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> quit;
Bye
[root@MySQL-Slave ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
[root@MySQL-Slave ~]# cd /var/lib/mysql
[root@MySQL-Slave mysql]# ls
auto.cnf client-cert.pem mysql.sock
binlog.000001 client-key.pem mysql.sock.lock
binlog.000002 ib_buffer_pool mysql_upgrade_info
binlog.000003 ibdata1 performance_schema
binlog.000004 ib_logfile0 private_key.pem
binlog.000005 ib_logfile1 public_key.pem
binlog.000006 ibtmp1 server-cert.pem
binlog.000007 #innodb_temp server-key.pem
binlog.000008 mysql sys
binlog.index mysql.ibd undo_001
ca-key.pem MySQL-Slave-relay-bin.000001 undo_002
ca.pem MySQL-Slave-relay-bin.index
[root@MySQL-Slave mysql]# pwd
/var/lib/mysql
[root@MySQL-Slave mysql]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.back
[root@MySQL-Slave mysql]# ls
auto.cnf.back binlog.000005 ca-key.pem ibdata1 mysql mysql.sock.lock server-cert.pem
binlog.000001 binlog.000006 ca.pem ib_logfile0 mysql.ibd mysql_upgrade_info server-key.pem
binlog.000002 binlog.000007 client-cert.pem ib_logfile1 MySQL-Slave-relay-bin.000001 performance_schema sys
binlog.000003 binlog.000008 client-key.pem ibtmp1 MySQL-Slave-relay-bin.index private_key.pem undo_001
binlog.000004 binlog.index ib_buffer_pool #innodb_temp mysql.sock public_key.pem undo_002
[root@MySQL-Slave mysql]# reboot
Last login: Wed Feb 27 01:26:47 2019 from 172.16.5.80
[root@MySQL-Slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 10
Server version: 8.0.15 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

No entry for terminal type "ģ

using dumb terminal settings.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.216
Master_User: mysqlslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000011
Read_Master_Log_Pos: 859
Relay_Log_File: MySQL-Slave-relay-bin.000003
Relay_Log_Pos: 319
Relay_Master_Log_File: binlog.000011
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 859
Relay_Log_Space: 533
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 798c1fc2-3765-11e9-bdc6-005056a4f1c0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

ERROR:
No query specified