MySQL主从复制
[root@localhost ~]# vim /etc/my.cnf #将server-id修改为1 默认为1 #开启blog日志,在server-id上面新增 # binlog 配置 server-id=1 #注意主从server-id不能一样 log-slave-updates=true #一般用于联机复制中, log-bin=master-bin #指定binlog日志名 [root@localhost ~]# service mysqld restart Redirecting to /bin/systemctl restart mysqld.service
修改Master的ID号和开启blog日志
[root@localhost ~]# vim /etc/my.cnf #将server-id修改为2(默认为1) #开启blog日志,在server-id上面新增 # binlog 配置 server-id=2 #server-id修改为2 log-bin=mysql-bin #slave的二进制日志 relay-log=relay-log-bin #中继日志文件名 relay-log-index=slave-relay-bin.index #中继日志的索引文件名 [root@localhost ~]# service mysqld restart Redirecting to /bin/systemctl restart mysqld.service
修改Slave的ID号和开启blog日志
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.27 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.16.20.226' IDENTIFIED BY '111111'; mysql> FLUSH PRIVILEGES;
mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 154 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
[root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.27-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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.20.199', MASTER_USER='root', MASTER_PASSWORD='111111', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=154; #配置主节点 mysql> CHANGE MASTER TO MASTER_HOST='172.16.20.199', MASTER_USER='root', MASTER_PASSWORD='111111', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.04 sec) mysql> show slave status; +----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | +----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ | | 172.16.20.199 | root | 3306 | 60 | master-bin.000001 | 154 | relay-log-bin.000001 | 4 | master-bin.000001 | No | Yes | | | | | | | 0 | | 0 | 154 | 154 | None | | 0 | No | | | | | | NULL | No | 1593 | 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. | 0 | | | 1 | | /usr/local/mysql/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | 191021 04:59:03 | | | | | | 0 | | | | +----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ 1 row in set (0.00 sec) mysql>
找到字段Slave_IO_Running和Slave_SQL_Running,如果Slave_IO_Running=找到字段Slave_IO_Running和Slave_SQL_Running,如果Slave_IO_Running=No
Slave_SQL_Running=Yes
当前Slave_IO_Running:值为No,向下查看错误信息:
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.
修改auto.cnf中的UUID值,随意更改一个值即可。
[root@localhost ~]# vim /usr/local/mysql/data/auto.cnf server-uuid=修改一下 [root@localhost ~]# systemctl restart mysqld 再次登录MySQL查看从节点信息 Slave_SQL_Running 和Slave_IO_Running值是否为Yes状态,表示配置成功
mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed
mysql> mysql> CREATE TABLE test -> ( -> id int, -> name varchar(255) -> ) -> ; Query OK, 0 rows affected (0.06 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.03 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec) mysql>
恭喜主从复制配置成功!!!