MySQL 5.5.50 master-slave replication-load Balancing

The environment is as follows:

master:ubuntu14.04-192.168.31.61, mysql5.5.50

slave:ubuntu14.04-192.168.31.66, mysql5.5.49

Database: MiFi

Database tables: Mf_group_members

Because the master-slave copy of MySQL itself is quite perfect, so here is not much to describe, directly to.

Modify Master's my.cnf file as follows:

Vim/etc/mysql/my.cnf

# the following can be used as easy to replay backup  Logs or for replication.# note: if you are setting up a  replication slave, see readme. Debian about#       other settings you may need  to change.server-id                = 61# the host IP address is recommended for convenient use log_bin                  = /var/log/mysql/mysql-bin.logbinlog-do-db=mifi# Database that needs to be synchronized binlog-ignore-db=mysql# ignored database binlog-ignore-db=information-schema# ignored database # In order to use transaction InnoDB maximum persistence and consistency in replication, You should specify the innodb_flush_log_at_trx_comm#it=1,sync_binlog=1 option  innodb_flush_log_at_trx_commit=1sync_binlog= 1expire_logs_days        = 10max_binlog_size         = 100m 

wq! Save after exiting

sudo service MySQL restart

Restart the MySQL service.

Create a user with replication permissions on host Master

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.31.66 ' identified by ' 123456 ';

Lock the host and record the location of the binary log (which will be used when the slave is set) Mysql>flush tables with read lock; Mysql> Show Master status;+------------------+----------+--------------+--------------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+--------------------------+|     mysql-bin.000001 | 1114 | MiFi | Mysql,information-schema |+------------------+----------+--------------+--------------------------+1 row in Set ( 0.00 sec)

Then export this data to/tmp/mifi.sql.

$ mysqldump-uroot-p MiFi >/tmp/mifi.sql$ SCP [email protected]:/tmp/mifi.sql./#拷贝主数据库sql文件.

Re-open Host Master write operation function

Mysql>unlock tables;

= "Modify slave slave configuration file (/etc/mysql/my.cnf)

# the following can be used as easy to replay backup  Logs or for replication.# note: if you are setting up a  replication slave, see readme. Debian about#       other settings you may need  to change.server-id                = 66#log_bin                         = /var/log/mysql/ mysql-bin.logrelay-log-index=slave-relay-bin.indexrelay-log=slave-relay-binreplicate-do-db= mifireplicate-ignore-db=mysqlreplicate-ignore-db=information-schemaexpire_logs_days         = 10max_binlog_size         =  100M

Initializing replication from machine slave

mysql> stop Slave; Query OK, 0 rows affected (0.01 sec) mysql> Change master to master_host= ' 192.168.31.61 ', master_user= ' Repl_use R ', master_password= ' 123456 ', and master_log_file= ' mysql-bin.000001 ', master_log_pos=1114; Query OK, 0 rows Affected (0.00 sec) #开启复制mysql > start slave; Query OK, 0 rows Affected (0.00 sec) View replication status mysql>show slave status\g

This completes the configuration of the master-slave copy of MySQL.

The test is as follows:

Main 192.168.31.61

mysql> Update mf_group_members set member_id=290 where id = 121;mysql> SELECT * from mf_group_members where id = 12 1;+-----+----------+-----------+-------------+| ID | group_id | member_id | Update_time |+-----+----------+-----------+-------------+|       121 | 35 |  290 | 1472005745 |+-----+----------+-----------+-------------+1 row in Set (0.00 sec)

From 192.168.31.66

Mysql> SELECT * from mf_group_members where id = 121;+-----+----------+-----------+-------------+| ID | group_id | member_id | Update_time |+-----+----------+-----------+-------------+|      121 | 35 | 290 | 1472005745 |+-----+----------+-----------+-------------+1 row in Set (0.00 sec)

A reminder of the problems that may be encountered in the middle:

160825 9:41:11 [ERROR] Slave I/o: Error connecting to master ' [email protected]:3306 '-retry-time:60 retries:86400, E rror_code:2003

Then I went to the server directly connected to the main database to find the problem:

$ mysql-urepl_user-p-h192.168.31.61enter password:error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.31.61 ' (1 11)

Workaround:

Instead of skip-networking The default is now-listen only on# localhost which are more compatible and are not less secure . #bind-address = 127.0.0.1//Write off this binding IP address #

And then it's ready.

This article from "for the Future" blog, declined reprint!

MySQL 5.5.50 master-slave replication-load Balancing