MySQL read/write separation, master-slave synchronous m-s Construction

Host:

[Email protected] ~]# mysql-usystem-p123456

mysql> CREATE DATABASE HA;

mysql> use HA;

Mysql> CREATE TABLE T1 (ID int,name varchar (20));

Service Mysqld Stop Lock table

Configuration my.cnf:

Vim/etc/my.cnf

Vim/etc/my.cnf

Log-bin=mysql-bin-master #启用二进制日志

Server-id = 1 #本机数据库ID mark

Binlog-do-db=ha #可以被从服务器复制的库. The name of the database in which the binary needs to be synchronized

Binlog-ignore-db=mysql #不可以被从服务器复制的库

To restart MySQL:

Service mysqld Restart

[Email protected] ~]# mysql-usystem-p123456

Authorized:

mysql> Grant replication Slave on * * to [e-mail protected] identified by "123456";

mysql> flush Privileges;

To view status information:
Mysql> Show master status;

Mysql> Show Binlog Events\g

Ensure that the data of the two machines is consistent before synchronizing.

[Email protected] ~]# mysqldump-usystem-p123456 HA >ha.sql

[Email protected] ~]# SCP ha.sql [email protected]:/root/

Slave machine:

[Email protected] ~]# mysql-usystem-p123456

mysql> CREATE DATABASE HA;

mysql-usystem-p123456 Ha

Modify the From server configuration file:

[Email protected] ~]# VIM/ETC/MY.CNF
Server-id = 2

Restart Mysqld

[Email protected] ~]# service mysqld restart

[Email protected] ~]# mysql-usystem-p123456

Mysql>change Master to master_host= ' 192.168.31.130 ', master_user= ' slave ', master_password= ' 123456 ';

mysql> start slave;

Mysql> Show slave status\g View status

Slave_io_running: One responsible for IO communication with the host
Slave_sql_running: Responsible for your own Slave MySQL process
Then view the status on the primary server:
Mysql> Show Processlist \g

Host:

mysql> INSERT INTO T1 values (1, ' CCC ');
Query OK, 1 row affected (0.01 sec)

Mysql> select * from T1;
+------+------+
| ID | name |
+------+------+
| 1 | QQQ |
| 1 | CCC |
+------+------+
2 rows in Set (0.00 sec)

Slave machine:

Mysql> select * from T1;
+------+------+
| ID | name |
+------+------+
| 1 | QQQ |
| 1 | CCC |

If you encounter the master never synchronizes, look at the location of the master-slave Bin-log, and then synchronize.

Execute the MySQL command from the server:

mysql> slave stop; #先停止slave服务

mysql> Change Master to master_log_file= ' mysqllog.000004 ', master_log_pos=106;

#根据上面主服务器的show the results of the master status, the binary database records from the server are returned to achieve the effect of synchronization

Mysql>slave start; #启动从服务器同步服务

Mysql> show Slave status\g; #用show slave status\g; Take a look at the synchronization from the server

Slave_io_running:yes

Slave_sql_running:yes

If yes, that means it's already in sync.

MySQL read/write separation, master-slave synchronous m-s Construction