MySQL Database master-Slave synchronization

Environment Description :

master database Master ip:192.168.1.205:3306

From the database slave ip:192.168.1.206:3306

Source installation Mysql-5.5.46 Please visit: http://linuxzkq.blog.51cto.com/9379412/1700082, not described here

###############################################################################

Configuration steps:

A. Primary Database configuration steps

1. Open the Binlog (binary log function) of the primary database and set the Server-id

2. Create account rep for synchronizing data

3. Lock table and view current log name and location (POS)

4. Back up all data from the current master database (fully prepared)

5. Unlock the master database and let the primary database continue to serve

6. Continue to write data to the master database

B. from the database configuration steps

1. Restore the full backup data from the primary database to the database

2. Set the Server-id from the database and make sure that this ID is not used by other MySQL services.

3, in the database to set the master-slave synchronization information, such as the primary database server IP address, port number, synchronization account, password, binlog file name, Binlog location (POS) point

4, start the master-slave synchronization of Start slave;

5, check whether the synchronization is successful, show slavestatus\g;

############################################################################

Primary Database Configuration :

##################################################################

Turn on the Binlog (binary logging feature) of the primary database and set the Server-id

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

Server-id = 1

Log-bin=mysql-bin

[Email protected] ~]# egrep "Server-id|log-bin"/etc/my.cnf

Server-id = 1

Log-bin=mysql-bin

##################################################################

Restart database

[Email protected] ~]# service mysqld restart

Shutting down MySQL ..... success!

Starting MySQL ..... success!

[Email protected] ~]# Netstat-tunlp|grep mysqld

TCP 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 16530/mysqld

[Email protected] ~]# lsof-i:3306

COMMAND PID USER FD TYPE DEVICE size/off NODENAME

Mysqld 16530 MySQL 11u IPv4 52627 0t0 TCP *:mysql (LISTEN)

##################################################################

See if binary logs are logged

[Email protected] ~]# ll/application/data/mysql-bin.000001

-RW-RW----. 1 mysql mysql 2629 Oct 2 15:35/application/data/mysql-bin.000001 #有此文件, proving that binary logs are logged

##################################################################

Create a test database and tables in the primary database

mysql> CREATE DATABASE test;

Query OK, 1 row affected (0.02 sec)

mysql> use test;

Database changed

Mysql> CREATE TABLE test (ID int (3), Namechar (10));

Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO test values (001, ' mytest ');

Query OK, 1 row affected (0.14 sec)

mysql> flush Privileges;

Query OK, 0 rows affected (0.02 sec)

##################################################################

Check to see if the Binlog (binary logging feature) is turned on

Mysql> Show variables like ' Log_bin ';

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

| variable_name | Value |

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

| Log_bin | On |

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

1 row in Set (0.00 sec)

##################################################################

Create an account rep for synchronizing data

mysql> grant replication Slave on * * to ' rep ' @ ' 192.168.1.% ' identified by ' rep123456 ';

mysql> flush Privileges;

Mysql> select User,host from Mysql.user;

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

| user | Host |

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

| Root | 127.0.0.1 |

| Rep | 192.168.1.% |

| Root | localhost |

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

3 rows in Set (0.04 sec)

##################################################################

Lock table

Mysql> flush Table Withread lock;

Query OK, 0 rows Affected (0.00 sec)

##################################################################

View the current binary log name and location (POS)

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|    mysql-bin.000001 |         107 |             | |

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

1 row in Set (0.03 sec)

##################################################################

Backing up data from the current primary database

[Email protected] ~]# mysqldump-uroot-p87003891214-a-b-f--master-data=2--events | gzip >/tmp/mysql_bak.$ (Date +%f). sql.gz

[Email protected] ~]# ll/tmp

Total 156

DRWXRWXRWT. 2 Tomcat tomcat 4096 OCT 2 09:33 hsperfdata_tomcat

-rw-r--r--. 1 root root 151317 Oct 2 20:08mysql_bak.2015-10-02.sql.gz

-RWXRWXRWT. 1 root root 0 11:55 yum.log

Unlocking the primary database

mysql> unlock tables;

Query OK, 0 rows Affected (0.00 sec)

##################################################################

from the database configuration :

######################################################

Restores full backup data from the primary database to the database

[Email protected] ~]# Scp/tmp/[email protected]:~

[email protected] ' s password:

MYSQL_BAK.2015-10-02.SQ 100% 148KB 147.8kb/s 00:00

[email protected] ~]# ll mysql_bak.2015-10-02.sql.gz

-rw-r--r--. 1 root root 151317 07:37 mysql_bak.2015-10-02.sql.gz

[Email protected] ~]# gzip-d mysql_bak.2015-10-02.sql.gz

[Email protected] ~]# mysql-uroot-p87003891214 < Mysql_bak.2015-10-02.sql

[Email protected] ~]# mysql-uroot-p87003891214-e "showdatabases;"

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

| Database |

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

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

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

[Email protected] ~]# mysql-uroot-p87003891214-e "Usetest;select * from Test;"

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

| ID | name |

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

| 1 | Mytest|

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

###############################################################

Set the Server-id from the database and make sure that this ID is not used by other MySQL services

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

Server-id = 2

[[email protected] ~]# grep "Server-id"/etc/my.cnf

Server-id = 2

##############################################################

Set up information about master-slave synchronization from the database, such as the IP address of the primary database server, port number, synchronization account, password, binlog file name, Binlog location (POS) point

Mysql> Change MASTER to

Master_host= ' 192.168.1.205 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' rep123456 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=107;

#############################################################

Started master-slave synchronization start slave;

mysql> start slave;

Check if sync is successful, show slave status\g;

Mysql> show Slavestatus\g;

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

Slave_io_state:waiting Formaster to send event

master_host:192.168.1.205

Master_user:rep

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000008

read_master_log_pos:107

relay_log_file:web2-relay-bin.000014

relay_log_pos:253

relay_master_log_file:mysql-bin.000008

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:107

relay_log_space:451

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:

Replicate_ignore_server_ids:

Master_server_id:1

1 row in Set (0.01 sec)

ERROR:

No query specified

The value of slave_io_running and slave_sql_running must be yes, which means that the master-slave synchronization is successful.

#############################################################

Continue inserting data into the test table

mysql> use test;

Database changed

mysql> INSERT INTO test values (002, ' mytest2 ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test values (003, ' mytest3 ');

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO test values (004, ' Mytest4 ');

Query OK, 1 row affected (0.04 sec)

View tables from a database

mysql> use test;

Database changed

Mysql> select * from test;

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

| ID | name |

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

| 1 | MyTest |

| 2 | Mytest2 |

| 3 | Mytest3 |

| 4 | Mytest4 |

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

4 rows in Set (0.00 sec)

MySQL Database master-Slave synchronization