mysql 5.6.10 32位 centos多实例_centos7mysql5.6.30单机多实例配置主主复制

1.在原来单实例的基础上修改配置/usr/my.cnf

[mysqld_multi]

mysqld=/usr/bin/mysqld_safe

mysqladmin=/usr/bin/mysqladmin

user=root

[mysqld3306]

port=3306

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

pid-file=/var/lib/mysql/localhost.localdomain.pid

datadir=/var/lib/mysql/

user=root

character_set_server=utf8

lower_case_table_names=1

max_connections=300

server-id=1

binlog-do-db=test

log-bin=master-bin

log-bin-index=master-bin.index

relay-log=slave-relay-bin

relay-log-index=slave-relay-bin.index

auto-increment-increment = 2

auto-increment-offset = 1

[mysqld3307]

port=3307

socket=/home/fuyouling/mutl_mysql/mysql_3307/mysql/mysql.sock

pid-file=/home/fuyouling/mutl_mysql/mysql_3307/mysql/localhost.localdomain.pid

datadir=/home/fuyouling/mutl_mysql/mysql_3307/mysql/

user=root

character_set_server=utf8

lower_case_table_names=1

max_connections=300

server_id=2

log-bin=master-bin

log-bin-index=master-bin.index

replicate-do-db=test

relay-log=slave-relay-bin

relay-log-index=slave-relay-bin.index

auto-increment-increment = 2

auto-increment-offset = 2

2.在3306服务器建立复制账户

mysqld_multi --defaults-extra-file=/usr/my.cnf start 3306 #开启3306服务

mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock        #登录mysql

mysql>grant replication slave,replication client on *.* to repl1@'localhost' identified by 'repl1'; #在主服务器上建立复制用户

mysql>flush privileges;  #刷新权限

3.在3307服务器上建立复制账户

mysqld_multi --defaults-extra-file=/usr/my.cnf start 3307 #开启3307服务

mysql -uroot -p654321 -S /home/fuyouling/mutl_mysql/mysql_3307/mysql/mysql.sock #登录mysql

mysql>grant replication slave,replication client on *.* to repl2@'localhost' identified by 'repl2'; #在主服务器上建立复制用户

mysql>flush privileges;  #刷新权限

4.在3306服务器上建立slave 连接master的信息

Change Master to Master_host='127.0.0.1',Master_port=3307,Master_user='repl2',Master_Password='repl2';

5.在3307从服务器上建立slave 连接master的信息

Change Master to Master_host='127.0.0.1',Master_port=3306,Master_user='repl1',Master_Password='repl1';

6.在3306上检查主从信息:

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000003 |      120 | test         |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: repl2

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 120

Relay_Log_File: slave-relay-bin.000008

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.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: 120

Relay_Log_Space: 621

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: 2

Master_UUID: 04793bcc-3e99-11e6-ab0c-000c29569f32

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)

7.在3307上检查主从信息

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000003 |      120 |              |                  |                   |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: repl1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 120

Relay_Log_File: slave-relay-bin.000007

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

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: 120

Relay_Log_Space: 621

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: be707b89-20cf-11e6-a8d0-000c29569f32

Master_Info_File: /home/fuyouling/mutl_mysql/mysql_3307/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)

8.测试

(1)

在3306上新建表test_table4

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_table1    |

| test_table2    |

| test_table3    |

+----------------+

3 rows in set (0.00 sec)

mysql> create table test_table4(id int,str varchar(100));

Query OK, 0 rows affected (0.03 sec)

登录3307上查看

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_table1    |

| test_table2    |

| test_table3    |

| test_table4    |

+----------------+

4 rows in set (0.00 sec)

(2)

在3307上创建新表test_table5

mysql> create table test_table5(id int,str varchar(100));

Query OK, 0 rows affected (0.05 sec)

在3306上查看

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| test_table1    |

| test_table2    |

| test_table3    |

| test_table4    |

| test_table5    |

+----------------+

5 rows in set (0.00 sec)

备注:这个其实是主主复制下的主动-主动模式。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值