centos7mysql5.6.30 single-Machine multi-instance configuration primary master replication

1. Modify the configuration on the basis of the original single instance/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. Set up a replication account on 3306 servers

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 [e-mail protected] ' localhost ' identified by ' repl1 '; #在主服务器上建立复制用户

Mysql>flush privileges; #刷新权限

3. Create a replication account on the 3307 server

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 [e-mail protected] ' localhost ' identified by ' repl2 '; #在主服务器上建立复制用户

Mysql>flush privileges; #刷新权限

4. Establish slave connection master information on 3306 servers

Change Master to master_host= ' 127.0.0.1 ', master_port=3307,master_user= ' repl2 ', master_password= ' repl2 ';

5. Establish the slave connection master from the server on 3307

Change Master to master_host= ' 127.0.0.1 ', master_port=3306,master_user= ' repl1 ', master_password= ' repl1 ';

6. Check the master/slave information on 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 have 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. Check the master/slave information on 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 have 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. Testing

(1)

Create a new table on 3306 test_table4

mysql> use test;

Reading table information for completion of table and column names

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)

Log on 3307 to view

mysql> use test;

Reading table information for completion of table and column names

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)

Create a new table on 3307 test_table5

Mysql> CREATE TABLE test_table5 (ID int,str varchar (100));

Query OK, 0 rows affected (0.05 sec)

View on 3306

Mysql> Show tables;

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

| Tables_in_test |

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

| Test_table1 |

| Test_table2 |

| Test_table3 |

| Test_table4 |

| Test_table5 |

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

5 rows in Set (0.00 sec)

Note: This is actually the active-active mode under the master replication.

This article is from the "Go to Sea" blog, please be sure to keep this source http://quguanhai.blog.51cto.com/1951497/1794659

centos7mysql5.6.30 single-Machine multi-instance configuration primary master replication