mysql5.6 one Master one from

mysql5.6 one Master one from

Reference

http://369369.blog.51cto.com/319630/790921/

MySQL Master-slave replication

Host Planning:

192.168.0.205edu-mysql-01 Master

192.168.0.206edu-mysql-02Salve

1, the master-slave server for the following actions:

1.1. Consistent version

1.2. Initialize the table and start MySQL in the background

1.3, change the root password

2. Modify Master server Master:

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

User=mysql

Pid_file =/var/lib/mysql/mysqld.pid

Socket =/var/lib/mysql/mysql.sock

Port = 3306

Basedir =/usr/local/mysql

DataDir =/var/lib/mysql

Tmpdir =/tmp

Log-bin = Master-bin

Log-bin-index = Master-bin.index

server_id = 1

Innodb_log_file_size = 256M

Expire-logs-days = 1

3. Modify the slave from the server:

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

User=mysql

Pid_file =/var/lib/mysql/mysqld.pid

Socket =/var/lib/mysql/mysql.sock

Port = 3306

Basedir =/usr/local/mysql

DataDir =/var/lib/mysql

Tmpdir =/tmp

server_id = 2

Relay_log_index = Slave_relay_bin.index

Relay_log = Slave_relay_bin

Innodb_log_file_size = 256M

Expire-logs-days = 1

4. Restart MySQL for two servers

/etc/init.d/mysql restart

5. Establish an account on the primary server and authorize slave:

#/usr/local/mysql/bin/mysql-uroot-p123456

Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ ' percent ' identified by ' q123456 '; Do not normally use root account

6, log on to the master server MySQL, query the status of master

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      master-bin.000009 |              320 |                  |                   | |

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

1 row in Set (0.00 sec)

Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

Change MASTER to master_log_file= ' mysql-bin.000004 ', master_log_pos=431;

Change Master to master_host= ' 192.168.0.32 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000004 ', master_log_pos=431;

7. Configure the slave from the server:

Mysql>change Master to master_host= ' 192.168.0.205 ', master_user= ' Mysync ', master_password= ' q123456 ', Master_log_ File= ' master-bin.000009 ', master_log_pos=320;

Mysql>start slave; To start the Copy from Server feature

8. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

MASTER_HOST:192.168.0.205//Primary server address

Master_user:mysync//Authorization account name, try to avoid using root

master_port:3306//Database port, some versions do not have this line

Connect_retry:60

Master_log_file:master-bin.000009

read_master_log_pos:760//#同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos

relay_log_file:slave_relay_bin.000002

relay_log_pos:724

relay_master_log_file:master-bin.000009

Slave_io_running:yes//This status must be Yes

Slave_sql_running:yes//This status must be Yes

......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

The above operation process, the master and slave server configuration is complete.

9, the master-slave server test:

master server MySQL, build the database, and create a table in this library to insert a piece of data:

mysql> CREATE DATABASE hi_db;

Query OK, 1 row Affected (0.00 sec)

mysql> use hi_db;

Database changed

Mysql> CREATE TABLE HI_TB (ID int (3), name char (10));

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO HI_TB values (001, ' Bobu ');

Query OK, 1 row Affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| Information_schema |

| hi_db |

| MySQL |

| Test |

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

4 rows in Set (0.00 sec)

MySQL Query from server:

mysql> show databases;

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

| Database |

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

| Information_schema |

|       hi_db | I ' M here, you see?

| MySQL |

| Test |

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

4 rows in Set (0.00 sec)

Mysql> Use hi_db

Database changed

Mysql> select * from HI_TB; View new specific data on the primary server

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

| ID | name |

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

| 1 | Bobu |

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

1 row in Set (0.00 sec)

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1893609

mysql5.6 one Master one from