MySQL Master from replication

Master-slave replication, steps are as follows:

It is assumed that the two MySQL servers that are primary from each other are deployed on different ports on the same machine respectively.

For example, the server IP is 58.83.223.20

Ports are: 3306 and 3307, respectively

In the case of each other, the two services are both master and slave, that is, the following description, if you describe the operation of master, then the server should do the corresponding operation. If you describe the operation of slave, then the server should do the corresponding operation.

1, the master-slave server for the following actions :
1.1, version consistent;
1.2, initialize the table, and start mysq in the background;
1.3, change the root password;
2. Modify master server Master:
My.ini
[Mysqld]
Log-bin=mysql-bin//[must]Enable binary logging
server-id=222//[must] server unique ID, default is 1, generally take IP last segment with database port
3. Modify the slave from the server:
My.ini
[Mysqld]
Log-bin=mysql-bin//[must]Enable binary logging
server-id=226//[must be] server unique ID, default is 1, usually take IP last paragraph

4. Restart MySQL for two servers
/bin/mysql restart

5.establish an account on the primary server and authorize slave:
Mysql3306/bin/mysql-uroot-proot
Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ '% ' identifiedby ' mysync '; //generally not rootAccount number, "%" means that all clients may be connected, as long as the account number, the password is correct, here can be specific client IP instead, such as 192.168.145.226, enhance security.

Mysql3307/bin/mysql-uroot-proot
Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ '% ' identifiedby ' mysync '; generally do not use the root account, "%" means that all clients may be connected, as long as the account, the password is correct, here can be specific client IP instead, such as 192.168.145.226, enhance security.

6 , log on to the master server for MySQL, and query the status of Master. The File and position here can be changed in real time.

Mysql3306/bin/mysql-uroot-proot
mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File | Position | binlog_do_db | binlog_ignore_db |
   +------------------+----------+--------------+------------------+
   |mysql-bin.000001 301|                 | |    
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Mysql3307/bin/mysql-uroot-proot

Mysql>show Master status;

+------------------+----------+--------------+ ------------------+
   | file             | Position | binlog_do_db | binlog_ignore_db |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001|      107 |             |                  |
   +------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)
Note: Do not operate the master server MySQL again after performing this stepto prevent the change of the primary server state value
7 , configure the slave from the server:

Mysql3306/bin/mysql-uroot-proot
mysql> Change master to master_host= ' 58.83.234.21 ', master_port=3307, master_user= ' Mysync ', master_password= ' Mysync ', master_log_file= ' mysql-bin.000001  ', master_log_pos=107;

Mysql3307/bin/mysql-uroot-proot

mysql> Change master to master_host= ' 58.83.234.21 ' , master_port=3306 , master_user= ' Mysync ', master_password= ' Mysync ', master_log_file= ' mysql-bin.000001  ', master_log_pos=301;

// Note Do not disconnect, "107" and "301" have no single quotation marks.

, master_log_file= 'mysql-bin.000001', master_log_pos=107; It is the show master status;

When two MySQL services are on the same machine,Master_port must be specified, otherwise it will appear

If something goes wrong:

Error 1201 (HY000): Could not initializemaster info structure; More error messages can is found in the MySQL error log .

The following direct write solution :

1mysql> Resetslave; # that's the point.
2.mysql> change Master to master_host= ' 58.83.234.21 ',master_port=3307, master_user= ' Mysync ', master_  Password= ' Mysync ', master_log_file= ' mysql-bin.000020 ', master_log_pos=107; # Please follow your own environment settings

re-set slave, master_log_file and Master_log_pos so you need to reset .
3.mysql> start slave; # it's normal.
   Mysql>start slave;   // start replication from server

Note: Changemaster to Syntax Description:
Changemaster tomaster_host= ' 192.168.x.x ', master_port=xxx,master_user= ' xxx ', master_password= ' xxx ', master_log_ File= ' mysql-bin.0000xxx ', master_log_pos=xxx;
Changemaster to option [, option] ...
Option
Master_host = ' host_name '
| Master_user = ' user_name '
| Master_password = ' PASSWORD '
| Master_port = Port_num
| Master_connect_retry = Interval
| Master_log_file = ' Master_log_name '
| Master_log_pos = Master_log_pos
| Relay_log_file = ' Relay_log_name '
| Relay_log_pos = Relay_log_pos
| Master_ssl = {0|1}
| Master_ssl_ca = ' Ca_file_name '
| Master_ssl_capath = ' Ca_directory_name '
| Master_ssl_cert = ' Cert_file_name '
| Master_ssl_key = ' Key_file_name '
| Master_ssl_cipher = ' cipher_list '
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:58.83.223.20//Primary server address
Master_user:myrync//authorized account name, try to avoid usingRoot
master_port:3306//database port, some versions do not have this line
Connect_retry:60
master_log_file:mysql-bin.000004
read_master_log_pos:600//#synchronously reads the location of the binary log, greater than or equal to>=exec_master_log_pos
relay_log_file:ddte-relay-bin.000003
relay_log_pos:251
relay_master_log_file:mysql-bin.000004
Slave_io_running:yes//This state must beYES
Slave_sql_running:yes//This state must beYES
......
Note:Slave_ioandSlave_sqlthe process must run correctly, i.e.YESstate, otherwise it is the wrong state(such as: one ofNOare wrong).
the above operation process, the master and slave server configuration is complete.
9, the master-slave server test:
Primary ServerMysql, set up a database, and insert a table in this library for a single 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)

from the serverMysqlEnquiry:
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; //you can see the specific data added on the master server
+------+------+
| ID | name |
+------+------+
| 1 | Bobu |
+------+------+
1 row in Set (0.00 sec)

MySQL Master from replication