MySQL主主从从配置_mysql主主配置-CSDN博客

本文链接：https://blog.csdn.net/PlatoWG/article/details/79611915

准备：

mysql安装包：MySQL-5.6.rpm.tar

5台linux主机：Red Hat Enterprise Linux Server release 6.7 (Santiago)

1、主机、IP清单

[root@master1 ~]# vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.4.10    master1.tedu.cn master1
192.168.4.11    master2.tedu.cn master2
192.168.4.12    slave1.tedu.cn  slave1
192.168.4.13    slave2.tedu.cn  slave2
192.168.4.14    monitor.tedu.cn monitor

2、通过scp把mysql安装包复制到5台机器

[root@room1pc01 ~]# for i in `seq 10 14`;do scp MySQL-5.6.rpm.tar 192.168.4.${i}:/opt; done
MySQL-5.6.rpm.tar                                                                   100%  206MB  11.5MB/s   00:18    
MySQL-5.6.rpm.tar                                                                   100%  206MB  12.9MB/s   00:16    
MySQL-5.6.rpm.tar                                                                   100%  206MB  12.9MB/s   00:16    
MySQL-5.6.rpm.tar                                                                   100%  206MB   9.4MB/s   00:22    
MySQL-5.6.rpm.tar                                                                   100%  206MB   7.4MB/s   00:28

3、5台机器以升级安装的方式安装mysql

[root@master1 opt]# rpm -Uvh MySQL-*.rpm
Preparing...                ########################################### [100%]
   1:MySQL-devel            ########################################### [ 14%]
   2:MySQL-client           ########################################### [ 29%]
   3:MySQL-test             ########################################### [ 43%]
   4:MySQL-embedded         ########################################### [ 57%]
   5:MySQL-shared-compat    ########################################### [ 71%]
   6:MySQL-shared           ########################################### [ 86%]
   7:MySQL-server           ########################################### [100%]
2018-03-19 07:15:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-03-19 07:15:50 4691 [Note] InnoDB: The InnoDB memory heap is disabled
2018-03-19 07:15:50 4691 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-03-19 07:15:50 4691 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-03-19 07:15:50 4691 [Note] InnoDB: Using Linux native AIO
2018-03-19 07:15:50 4691 [Note] InnoDB: Not using CPU crc32 instructions
2018-03-19 07:15:50 4691 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-03-19 07:15:50 4691 [Note] InnoDB: Completed initialization of buffer pool
2018-03-19 07:15:50 4691 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2018-03-19 07:15:50 4691 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2018-03-19 07:15:50 4691 [Note] InnoDB: Database physically writes the file full: wait...
2018-03-19 07:15:51 4691 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-03-19 07:15:53 4691 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-03-19 07:15:55 4691 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-03-19 07:15:55 4691 [Warning] InnoDB: New log files created, LSN=45781
2018-03-19 07:15:55 4691 [Note] InnoDB: Doublewrite buffer not found: creating new
2018-03-19 07:15:56 4691 [Note] InnoDB: Doublewrite buffer created
2018-03-19 07:15:56 4691 [Note] InnoDB: 128 rollback segment(s) are active.
2018-03-19 07:15:56 4691 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-03-19 07:15:56 4691 [Note] InnoDB: Foreign key constraint system tables created
2018-03-19 07:15:56 4691 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-03-19 07:15:56 4691 [Note] InnoDB: Tablespace and datafile system tables created.
2018-03-19 07:15:56 4691 [Note] InnoDB: Waiting for purge to start
2018-03-19 07:15:56 4691 [Note] InnoDB: 5.6.15 started; log sequence number 0
A random root password has been set. You will find it in '/root/.mysql_secret'.
2018-03-19 07:16:02 4691 [Note] Binlog end
2018-03-19 07:16:02 4691 [Note] InnoDB: FTS optimize thread exiting.
2018-03-19 07:16:02 4691 [Note] InnoDB: Starting shutdown...
2018-03-19 07:16:04 4691 [Note] InnoDB: Shutdown completed; log sequence number 1625977


2018-03-19 07:16:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-03-19 07:16:04 4714 [Note] InnoDB: The InnoDB memory heap is disabled
2018-03-19 07:16:04 4714 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-03-19 07:16:04 4714 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-03-19 07:16:04 4714 [Note] InnoDB: Using Linux native AIO
2018-03-19 07:16:04 4714 [Note] InnoDB: Not using CPU crc32 instructions
2018-03-19 07:16:04 4714 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-03-19 07:16:04 4714 [Note] InnoDB: Completed initialization of buffer pool
2018-03-19 07:16:04 4714 [Note] InnoDB: Highest supported file format is Barracuda.
2018-03-19 07:16:04 4714 [Note] InnoDB: 128 rollback segment(s) are active.
2018-03-19 07:16:05 4714 [Note] InnoDB: Waiting for purge to start
2018-03-19 07:16:05 4714 [Note] InnoDB: 5.6.15 started; log sequence number 1625977
2018-03-19 07:16:05 4714 [Note] Binlog end
2018-03-19 07:16:05 4714 [Note] InnoDB: FTS optimize thread exiting.
2018-03-19 07:16:05 4714 [Note] InnoDB: Starting shutdown...
2018-03-19 07:16:07 4714 [Note] InnoDB: Shutdown completed; log sequence number 1625987




A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

4、5台机器启动mysql服务，登录mysql并修改初始密码

[root@master1 opt]# cat /root/.mysql_secret 
# The random password set for the root user at Mon Mar 19 07:16:00 2018 (local time): 4gKC81Nr

[root@master1 opt]# /etc/init.d/mysql status
 ERROR! MySQL is not running
[root@master1 opt]# /etc/init.d/mysql start
Starting MySQL........ SUCCESS! 
[root@master1 opt]# mysql -uroot -p
Enter password:     #输入安装mysql时系统自动生成的初始密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15

Copyright (c) 2000, 2013, 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> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password("123456");
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

5、master1、master2配置为主主

1）在master1、master2上修改my.cnf配置，启用bin-log日志

[root@master1 opt]# vim /etc/my.cnf 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log_bin=master1
server_id=10

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@master1 opt]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL........... SUCCESS!

2）在master1的mysql服务中启用链式复制，并重启mysql使配置生效

[root@master1 opt]# vim /etc/my.cnf 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log_bin=master1
server_id=10
log_slave_updates

[mysqld_safe]
log-error=/var/log/mysqld.log

[root@master1 opt]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.................... SUCCESS!

3）在master1、master2上登录mysql，查看配置是否生效

[root@master1 opt]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| master1.000003 |      120 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

mysql>

4）在master1、master2上对进行同步的账号授权

mysql> grant replication slave on *.* to slaveuser@"192.168.4.%" identified by "abc123";
Query OK, 0 rows affected (0.04 sec)

5）配置master1、master2为主主，检查核实slave状态Slave_IO_Running: Yes Slave_SQL_Running: Yes

mysql>  change master to master_host="192.168.4.11",master_user="slaveuser",master_password="abc123",master_log_file="master2.000001",master_log_pos=333;  
Query OK, 0 rows affected, 2 warnings (0.53 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.11
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master2.000001
          Read_Master_Log_Pos: 333
               Relay_Log_File: master1-relay-bin.000002
                Relay_Log_Pos: 281
        Relay_Master_Log_File: master2.000001
             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: 333
              Relay_Log_Space: 456
              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: 11
                  Master_UUID: 1fcc51f4-2b04-11e8-b606-545201011101
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

mysql>

6、配置slave1、slave2为master1的从

1）在slave1、slave2上启用server_id，并重启mysql，确保配置生效

[root@slave1 opt]# vim /etc/my.cnf 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=12

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@slave1 opt]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.............. SUCCESS!

2）配置slave1、slave2为master1的从

[root@slave1 opt]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> show slave status\G
Empty set (0.02 sec)

mysql> change master to master_host="192.168.4.10",master_user="slaveuser",master_password="abc123",master_log_file="master1.000003",master_log_pos=333;  
Query OK, 0 rows affected, 2 warnings (0.43 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.10
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1.000003
          Read_Master_Log_Pos: 333
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 281
        Relay_Master_Log_File: master1.000003
             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: 333
              Relay_Log_Space: 455
              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: 10
                  Master_UUID: 8fdcff76-2b02-11e8-b5fb-545201011001
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

mysql>

7、测试主主从从同步效果

1）在master1上创建新库bbsdb，并授权bbs用户测试主主从从同步效果

mysql> create database bbsdb;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbsdb              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> grant all on bbsdb.* to bbs@"192.168.4.%" identified by "qwe123";
Query OK, 0 rows affected (0.04 sec)

mysql>

2）在master2、slave1、slave2查看同步效果

mysql> system hostname;
master2.tedu.cn
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbsdb              |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3）在monitor上使用bbs用户登录master1，并在bbsdb库下创建t1表，测试同步效果

[root@monitor opt]# mysql -h192.168.4.10 -ubbs -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.15-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbsdb              |
| test               |
+--------------------+
3 rows in set (0.02 sec)

mysql> create table bbsdb.t1 (id int(3) zerofill);
Query OK, 0 rows affected (0.43 sec)

mysql> insert into bbsdb.t1 values(2);
Query OK, 1 row affected (0.10 sec)

mysql> select * from bbsdb.t1;
+------+
| id   |
+------+
|  002 |
+------+
1 row in set (0.01 sec)

mysql>

4）在master2、slave1、slave2上查bbsdb.t1上的数据同步情况

mysql> select * from bbsdb.t1;
+------+
| id   |
+------+
|  002 |
+------+
1 row in set (0.00 sec)

mysql> system hostname;
slave2.tedu.cn
mysql>