MySQL Master server Setup

When traffic is too large, one of our servers can be hard to load, and we need to use the configuration of the master-slave server.

The specific configuration is as follows:

172.17.10.57-bit primary server

172.17.55.206 from the server

After the correct installation of the database, ensure the interoperability of the two units.

Mysql>grant REPLICATION SLAVE on * * to ' mysync ' @ ' percent ' identified by ' q123456 ';

Generally do not use the root account, "%" means that all clients may be connected, as long as the account, the password is correct, the specific client IP can be replaced here, such as 192.168.145.226, enhance security

Test first to see if you can connect correctly. A clear understanding of the rights management of the data is also needed.

The primary server is configured as follows:

In/etc/my.cn

#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
server-id=222//[must be] server unique ID, default is 1, usually take IP last paragraph

3. Modify the slave from the server:
#vi/etc/my.cnf
[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
/etc/init.d/mysql restart

5 Viewing status

Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|      mysql-bin.000006 |              331 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

6 Connecting to the master server from the server

mysql> Change Master to master_host= ' 172.17.10.57 ', master_user= ' test ', master_password= ' passwd ', -master_log_file= ' mysql-bin.000006 '; mysql> start slave mysql> show slave status\g;

mysql> Change Master to Master_host = ' 172.17.10.57 ', master_user = ' Mysync ', master_password= ' q123456 ', Master_log_ File= ' mysql-bin.000019 ', master_log_pos=120;

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.2.222//Primary server address

Master_user:myrync//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:mysql-bin.000004

read_master_log_pos:600//#同步读取二进制日志的位置, 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 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.

If there's an error, remember to come here and find
MySQL Mutual master from replication FAQ

Source: Posted:2012-08-19 17:15 click:

Error: 1) Change master caused: Last_io_error:error connecting to master-retry-time:60 retries 2) Stop slave process without unlocking: Stop SL Ave ERROR 1192 (HY000): Cant execute the given command because you have active locked tables

Error:
1)
Change Master results in:
Last_io_error:error Connecting to master-retry-time:60 retries

2)
To stop the slave process without unlocking:
> Stop slave;
ERROR 1192 (HY000): Can ' t execute the given command because you had active locked tables or an active transaction

3)
Change master syntax error, comma dropped
Mysql> Change Master to
-Master_host= ' IP '
-master_user= ' user ',
Master_password= ' PASSWD ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=106;
Error 1064 (42000): You have a error in your SQL syntax; Check the manual, corresponds to your MySQL server version for the right syntax to use near ' master_user= ' user ',
Master_password= ' PASSWD ',
Master_log_file= ' mysql-bin.000002 ' at line 3

4)
Change master without stopping the slave process
mysql> Change Master to master_host= ' IP ', master_user= ' user ', master_password= ' PASSWD ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=106;
ERROR 1198 (HY000): This operation cannot is performed with a running slave; Run STOP SLAVE First

5)
The Server-id of a B are the same:
Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server IDs;
These IDs must be different for replication to work (or the--replicate-same-server-id option must is used on
Slave but this does don't always make sense; Please check the manual before using it).
View Server-id
Mysql> Show variables like ' server_id ';
Manually modifying Server-id
mysql> set global server_id=2; #此处的数值和my. CNF, just set it up.
mysql> slave start;

6) After change master, check the status of slave and discover that slave_io_running is no
It is important to note that the MySQL process is finally restarted after the above operation has been completed.

MySQL Master server Setup