mysql主主复制

最新推荐文章于 2023-10-08 22:05:31 发布

原创最新推荐文章于 2023-10-08 22:05:31 发布 · 946 阅读

0 ·

CC 4.0 BY-SA版权

Linux服务器专栏收录该内容

10 篇文章

订阅专栏

server1:IP=192.168.1.103,port=3306

server2;IP=192.168.1.100,port=3306

step1:分别新建slave账户

grant replication slave on *.* to 'backup'@'%' identified by 'backup'

step2：/etc/mysql/my.cnf配置

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
binlog_do_db            =test2
binlog_ignore_db        =mysql
replicate_do_db         =test2
replicate_ignore_db=mysql,information_schema

log_slave_updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

binlog_do_db=test2
binlog_ignore_db=mysql
replicate_do_db=test
replicate_ignore_db=mysql,information_schema
log_slave_updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2
slave_skip_errors=all

step3：重启mysql服务

root@ubuntu:~# service mysql restart
mysql start/running, process 2458

root@ubuntu:~# service mysql restart
mysql start/running, process 1626

step4：查看master status

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 106
    Binlog_Do_DB: test2
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.000002
        Position: 106
    Binlog_Do_DB: test2
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

step5：修改master

mysql> change master to 
    -> master_host ='192.168.1.100',
    -> master_port =3306
    -> ,master_user='backup',
    -> master_password='backup',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=106;

mysql> change master to 
    -> master_host='192.168.1.103',
    -> master_port=3306,
    -> master_user='backup',
    -> master_password='backup',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=106;

step6：查看slave status

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 106
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test2
          Replicate_Ignore_DB: mysql,information_schema

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.103
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 106
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql,information_schema

step7：测试