MySQL---master-slave replication

1.1 MySQLmaster-slave replication:

Environment Planning : There are DB01 servers in the cluster as the database master server , because of the notebook space problem here the Backup server as Slave server , Don't install new machines , save some space , deploy MySQL on the backup server

Description : The premise of the implementation of master-slave replication is the two server synchronization to coincide , the front NTP time server has been set up, there is no such issue

1.1.1Primary server Configuration:

1. Modify the configuration file

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

server_id = 1

Log-bin=master-bin

Log-slave-updates=true

2. Restart the database

[Email protected] ~]#/etc/init.d/mysqld restart

3. access to the master database for authorization

mysql> grant replication Slave on * * to ' myslave ' @ ' 172.16.1.0/255.255.255.0 ' identified by ' oldboy123 ';

Query OK, 0 rows Affected (0.00 sec)

4. Display position information , and make changes from the server side again

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      master-bin.000001 |              120 |                  |                   | |

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

1 row in Set (0.00 sec)

1.1.2from server configuration:

1. Modify the configuration file

[Email protected] bin]# TAIL/ETC/MY.CNF

server_id = 2

Relay-log=relay-log-bin

Relay-log-index=slave=relay-bin.index

2. start the MySQL service

3. Configure the master-log-file and master-log-pos parameters According to the master server

mysql> Change Master to master_host= ' 172.16.1.51 ', master_user= ' myslave ', master_password= ' oldboy123 ', Master_log_ File= ' master-bin.000001 ', master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:172.16.1.51

Master_user:myslave

master_port:3306

Connect_retry:60

master_log_file:master-bin.000002

read_master_log_pos:120

relay_log_file:relay-log-bin.000002

relay_log_pos:284

relay_master_log_file:master-bin.000002

Slave_io_running:yes

Slave_sql_running:yes must demonstrate that the master-slave replication configuration is successful

1.1.3encountered an error:ViewSlavestatus is displayedSlave_io_running:no

Show IO as No when viewing the slave status from the library side

Mysql> Show Slave Status\g

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

Slave_io_state:

master_host:172.16.1.51

Master_user:myslave

master_port:3306

Connect_retry:60

master_log_file:master-bin.000001

read_master_log_pos:1645

relay_log_file:relay-log-bin.000001

Relay_log_pos:4

relay_master_log_file:master-bin.000001

Slave_io_running: No

Slave_sql_running:yes

This is mainly due to the difference between the file and pos values on the main library side and the settings from the library side , the solution is as follows :

Master side :

1. Restart the database service

[Email protected] ~]#/etc/init.d/mysqld restart

Shutting down MySQL. success!

Starting MySQL .... success!

2. View file and pos values

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      master-bin.000002 |              120 |                  |                   | |

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

1 row in Set (0.02 sec)

Slave End :

1. Enter the database and stop Slave

mysql> stop Slave;

Query OK, 0 rows affected (0.02 sec)

2. re -write the file and pos values :

mysql> Change Master to master_log_file= ' master-bin.000002 ', master_log_pos=120;

Query OK, 0 rows affected (0.04 sec)

3. start Slave:

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

4. view slave status

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:172.16.1.51

Master_user:myslave

master_port:3306

Connect_retry:60

master_log_file:master-bin.000002

read_master_log_pos:120

relay_log_file:relay-log-bin.000002

relay_log_pos:284

relay_master_log_file:master-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

1.1.4to test:

1. Create a database on the master side

mysql> CREATE Database Jiang;

Query OK, 1 row affected (0.03 sec)

mysql> show databases;

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

| Database |

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

| Information_schema |

| Dedecms |

| Discuz |

| Jiang |

| MySQL |

| Performance_schema |

| Test |

| Test2 |

| Test3 |

| WordPress |

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

Rows in Set (0.01 sec)

2. View the database on the slave side:

mysql> show databases;

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

| Database |

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

| Information_schema |

|                Jiang | MySQL master-slave replication configuration succeeded !

| MySQL |

| Performance_schema |

| Test |

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

5 rows in Set (0.00 sec)

MySQL---master-slave replication