MySQL master-Slave synchronization experiment Fine Solution

MySQL master-Slave synchronization experiment Fine Solution

I. Experimental environment

Lab environment 192.168.9.108 to master

192.168.9.109 to Slave

Database versions: Version 5.1.73

Installation method: The source of the Yum installation source is 163

System version: CentOS 6.5

1. View the system version

[Email protected] ~]# cat/etc/issue

CentOS Release 6.5 (Final)

Kernel \ r on an \m

Two. Preparation of the experiment

1. Installing the MySQL Service

[Email protected] opt]# ansible-i hosts tomcatserver-m yum-a "Name=mysql-server state=present"

Note: Here I have previously installed ansible, here Unified installation.

Name here to write Mysql-server

2. Start the MySQL service

[Email protected] opt]# ansible-i hosts tomcatserver-m service-a ' Name=mysqld state=started '

3. Create a password for the MySQL database

mysql> Set password for [email protected] = password (' 123456 ');

4. Configure the primary database to create a database that needs to be synchronized

[Email protected] opt]# mysql-uroot-p123456

mysql> CREATE DATABASE HA;

mysql> use HA;

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

Three. Configure the primary server

1. Configuring the My.cnf File

Vim/etc/my.cnf

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

Datadir=/var/lib/mysql #数据的存放点

Socket=/var/lib/mysql/mysql.sock

User=mysql #用户

Log-bin=mysql-bin-master # Start binary log

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

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

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

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[Mysqld_safe]

Log-error=/var/log/mysqld-bin-master.log

Pid-file=/var/run/mysqld/mysqld-bin-master.pid

2. Restart the database

To restart after modifying the database

Service mysqld Restart

3. Authorization from the user can login

mysql> grant replication Slave on * * to Sla[email protected] identified by "123456";

4. View status information

Mysql> Show master status;

+-------------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------------+----------+--------------+------------------+

|      mysql-bin-master.000001 | 106 | HA | MySQL |

+-------------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

View binary logs (this is configured in the config file)

[Email protected] ~]# ls/var/lib/mysql/

HA ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin-master.000001 mysql-bin-master.index mysql.sock test

Mysql> Show Binlog Events \g

1. Row ***************************

log_name:mysql-bin-master.000001

Pos:4

Event_type:format_desc

Server_id:1

end_log_pos:106

Info:server Ver:5.1.73-log, Binlog ver:4

1 row in Set (0.00 sec)

5. Database on the server to ensure consistent database synchronization

[Email protected] ~]# mysqldump-uroot-p123456 HA > Ha.sql

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

Ha.sql

Four. Configure the slave server

1. Test whether the link to the master server is successful

[Email protected] ~]# mysql-uslave-p123456-h 192.168.9.108

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 11

Server Version:5.1.73-log Source Distribution

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Test |

+--------------------+

2 rows in Set (0.00 sec)

The HA database is not visible here, only the copy permission

2. Import the database and maintain consistency with the primary database server

mysql> CREATE DATABASE HA;

Mysql> mysql-uroot-p123456 Ha

3. Modify the configuration file from the server

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

server-id=2# from the server ID number, not the same as the primary ID, if you set multiple slave servers, each slave server must have a unique Server-id value that must be different from the primary server and the other from the server. Server-id values can be considered similar to IP addresses: These ID values uniquely identify each server in the replication server cluster

master-host=192.168.9.108 #指定主服务器IP Address

Master-user=slave #指定住服务器上可以进行同步的用户名称

master-password=123456 # Specify password

master-port=3306

Master-connect-retry=60 #断点重新链接时间

[Mysqld_safe]

Log-error=/var/log/mysqld-slave.log

Pid-file=/var/run/mysqld/mysqld-slave.pid

4. Restart the database

Service mysqld Restart

Note: Here to reboot if direct boot from service will error as follows

mysql> start slave;

ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MySQL error log

Solution:

mysql> slave stop;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> Reset Slave;

Query OK, 0 rows Affected (0.00 sec)

mysql> Change Master to master_host= ' 192.168.1.63 ', master_user= ' slave ', master_password= ' 123456 ';

mysql> start slave; Start from

Mysql> Show slave status\g View status

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.9.108

Master_user:slave

master_port:3306

Connect_retry:60

master_log_file:mysql-bin-master.000001

read_master_log_pos:106

relay_log_file:mysqld-slave-relay-bin.000002

relay_log_pos:258

relay_master_log_file:mysql-bin-master.000001

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:106

relay_log_space:420

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

Slave_io_running: One responsible for IO communication with the host

Slave_sql_running: Responsible for your own Slave MySQL process

Five. View status on the primary server

Mysql> Show Processlist \g

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect ...

Connection id:10

Current Database:ha

1. Row ***************************

Id:6

User:root

Host:localhost

Db:null

Command:sleep

time:1480

State:

Info:null

2. Row ***************************

Id:9

User:slave

host:192.168.9.109:33320

Db:null

Command:binlog Dump

Time:51

State:has sent all binlog to slave; Waiting for Binlog to be updated

Info:null

3. Row ***************************

Id:10

User:root

Host:localhost

Db:ha

Command:query

time:0

State:null

Info:show processlist

1. Primary database insert data for testing

mysql> INSERT INTO T1 values (1, ' Tianhe ');

2. Viewing from a database

Mysql> SELECT * from HA. T1;

+------+--------+

| ID | name |

+------+--------+

| 1 | Tianhe |

+------+--------+

1 row in Set (0.00 sec

Six. troubleshooting

If you encounter the master never synchronizes, look at the principal and subordinate Bin-log location, and then sync again.

Mysql> Show master status;

+-------------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------------+----------+--------------+------------------+

|      mysql-bin-master.000001 | 200 | HA | MySQL |

+-------------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

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.

Restart the server, and then view the status:

Stop slave stop from the server;

Open from server slave start;

Troubleshooting ideas:

1. The binary log is not turned on

2, IPTABLES not release the port

3. The corresponding host IP address was incorrectly written

SQL thread Error

1, the master-slave server database structure is not unified

After the error, the data is small, you can manually resolve the creation of the insert, and then update the slave state.

Note: If the Lord mistakenly deleted it.  Then it was mistakenly deleted from the top. #因此主上要定期做mysqldump备份.

Seven. Complete

Master-Slave synchronization here to complete the use of script to monitor, monitoring Slave Two Yes (Slave_io and slave_sql process), such as the discovery of only one or 0 yes, it indicates the master-slave problem, send SMS alarm.

[[email protected] ~]# cat mysql_master.sh#!/bin/ bashport= ' netstat -anl|grep 3306 |sed -n  ' 1p '  |awk  ' {print $4} ' |awk  -F:  ' { print $2} ' array= ($ (mysql -uroot -p123456 -e ) Show slave  status\g "|grep " Running " |awk  ' {print $2} ') if [ " $port " == " 3306 " ]then  if [  ${array[0]}"  ==  "Yes"  ] | |  [  "${array[1]}"  ==  "Yes"  ]    then       echo  "Slave is ok"     else      echo   "Slave is error"   fifi 

This article is from the "Innocence" blog, be sure to keep this source http://innocence.blog.51cto.com/4313888/1963300

MySQL master-Slave synchronization experiment Fine Solution