Centos6.4 + mysql-5.6.38-linux-glibc2.12-x86_64.tar for MySQL master-slave replication

MySQL installation method :
Http://www.cnblogs.com/lin3615/p/4376224.html

Two servers are used.

Master:192.168.1.106

from:192.168.1.69

1. Create a new user in MySQL in the main library server to use INSERT into Mysql.user (Host,user,password,ssl_cipher,x509_issuer,x509_subject) from the library connection VALUES ('% ', ' rjj123 ', password (' 123456 '), ', ', ', ');

2. #刷新系统授权表flush privileges;  

3. #授权用户rjj只能从 192.168.1.69 This IP access to the primary server 192.168.1.106 above the database grant replication slave on  * * to ' rjj123 ' @ ' 192.168.1.69 ' Identified by ' 123456 ';

rjj123: User name, the account used to log in to the master service from the server

192.168.1.69: IP from the service

123456: password for account

4. First copy the database of the primary server to the slave server, the method can refer to: http://www.cnblogs.com/lin3615/p/3749438.html

This step is to create the same database information in the main service and from the service, the same as the database, the table structure

5. Configure the MySQL master server (192.168.1.106) my.cnf file vim/etc/my.cnf  
The configuration is all in my my.cnf, mostly red .

 [mysqld "#主机的东西 BINLOG-DO-DB=RJJ binlog-ignore-db= MySQL log-bin=mysql-bin  user = Mysqlbasedir =/usr/local/mysql  Skip-external-lockingkey_buffer = 384mmax_allowed_packet=268435456max_connections = 500max_user_connections = 35wait _timeout=40connect_timeout=10# Table_cache = 512sort_buffer_size = 2mread_buffer_size = 2Mread_rnd_buffer_size = 8mmyisam_sort_buffer_size = 64mthread_cache_size = 8query_cache_size = 32mthread_concurrency = 8# host server-id = 106  # skip-innodb[mysqldump]quickmax_allowed_packet = 16m[mysql]no-auto-rehash[myisamchk] Key_buffer = 256msort_buffer_size = 256mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout  

Generally as long as these are the parameters server-id=1   #设置服务器id, 1 for the primary server, generally with the IP address to replace Log-bin=mysql-bin  #启动MySQ二进制日志系统binlog-do-db=test  #需要同步的数据库名, if you have more than one database, repeat this parameter for each database row Binlog-ignore-db=mysql   #不同步mysql系统数据库 # Save exit service mysqld  restart  #重启MySQLmysql-H localhost-u root-p   #登陆show variables like ' server_id ';  Whether the value of the #查看server-id is 1mysql> show variables like ' server_id ';

6.show master status;  #查看主服务器, a message similar to the following appears

You need to remember this mysql-bin.000014 and 120 in the master server Note: The value of file is remembered here: the value of mysql-bin.000011 and position: 107, the heavy server configuration is used.

The MySQL configuration from the service first needs to configure the/ETC/MY.COF vim/etc/my.cnf   #编辑配置文件, add the following in the [mysqld] section server-id=2   #设置服务器id, modify its value to 2, Expressed as #需要同步的数据库名 from the database log-bin=mysql-bin  #启动MySQ二进制日志系统replicate-do-db=test,   If there are multiple databases, you can repeat this parameter, one row per database
Replicate-ignore-db=mysql   #不同步mysql系统数据库read_only  #设置数据库只读service mysqld Restart   # Restarting MySQL from the server is basically the same as MySQL, read_only  #设置数据库只读, this configuration is directly from the service set to read-only.

Now start configuring mysql# from the service stop slave sync process stop slave; #执行同步语句change Master to master_host= ' 192.168.1.106 '//Host's ip,master_user= ' rjj123 '//Our authorized account on the host, master_password= ' 123456 '//password, master_log_file= ' mysql-bin.000014 '//We get the file on the main server, master_log_pos=120; Position

#开启slave同步进程start slave; Note: The  show slave status\g command is displayed without opening the start slave

After we execute start slave, we execute the SHOW slave status\g command again.

Slave_io_running:yes

Slave_sql_running:yes

Both of these parameters are Yes .

If you execute show slave status\g; appears to be

Slave_io_running:connecting

This problem, the Internet to find a lot

It's usually the answer.

Mine is not the problem, I put the primary server and the firewall from the server are shut down to

The way to turn off the firewall is: 1. Permanent open: Chkconfig iptables on off: Chkconfig iptables off2. Immediate effect, restart after restart: Service iptables start off: Service iptables stop need to explain that the other services under Linux can be performed with the above command to open and close operation Supplement: A. The firewall also needs to shut down the IPv6 firewall: Chkconfig ip6tables off and can view the status with the following command: Chkconfig--list iptablesb. The SELinux status can be viewed using the following command: sestatus===============

Well configured master-slave replication, let's test to see if it will succeed? The main library of the table information, this is the data

There is no data from the library table

The configuration is synchronized, but the main library's data cannot be synchronized when viewed from the library.

These data are original, configured well, not inserted data test, directly in from the library query.

Data from the main library was later deleted. Now the main library and the data from the library are consistent. Let's insert a data test to see

Main Library Insert Data

Now look at whether there is data from the library

OK, now the data is in sync.

Centos6.4 + mysql-5.6.38-linux-glibc2.12-x86_64.tar for MySQL master-slave replication