MySQL Master-slave replication

The environment is a previously configured lnmp+keepalived load balancer of 2 machines, IP is 10.10.10.13 and 10.10.10.14 respectively.

1 , Login system

Last Login:sun Jul 23:36:55 from 10.10.10.1

[Email protected] ~]# NETSTAT-NLTP

Active Internet connections (only servers)

Proto recv-q send-q Local address Foreign address State Pid/program Name

TCP 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 9471/php-fpm

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

TCP 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 11021/nginx

TCP 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1961/sshd

TCP 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 1821/cupsd

TCP 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2115/master

TCP 0 0::: $:::* LISTEN 1961/sshd

TCP 0 0:: 1:631:::* LISTEN 1821/CUPSD

TCP 0 0:: 1:25:::* LISTEN 2115/master

Note: MySQL version of master and slave machine is consistent, and both have initialized the table and set the root password.

2 , modifying the master-slave server configuration file 2 a field

#vi/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin//Enable binary logging

Server-id=1//server unique ID, default is 1, where the main use of the default, from the server to 2. After

# Service Mysqld Restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

3 , establish an account on the primary server and authorize Slave:

#mysql-uroot-p

Enter Password:

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

Your MySQL Connection ID is 2

Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>grant REPLICATION SLAVE on * * to ' sync ' @ ' percent ' identified by ' 123456 ';

Query the status of Master and record The values of FILE and Position

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000002 |              251 |                  | |

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

1 row in Set (0.00 sec)

4 , configure the slave server

# mysql-uroot-p

Enter Password:

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

Your MySQL Connection ID is 2

Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Change Master to master_host= ' 10.10.10.13 ', master_user= ' sync ', master_password= ' 123456 ', master_log_file= ' My

Sql-bin.000002 ', master_log_pos=251;

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:10.10.10.13

Master_user:mysync

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:251

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000002

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

relay_log_space:407

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)

The key is that the slave_io and slave_sql processes must be YES

5 , master-slave replication test

first in the main MySQL on:

mysql> CREATE DATABASE fuck;

Query OK, 1 row affected (0.03 sec)

mysql> use fuck;

Database changed

Mysql>

Mysql>

mysql> CREATE TABLE fuck (id int (3), name char (10));

Query OK, 0 rows affected (0.08 sec)

Mysql>

Mysql>

mysql> insert INTO fuck values (001, ' ABCD ');

Query OK, 1 row Affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| Information_schema |

| Fuck |

| MySQL |

| Test |

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

4 rows in Set (0.03 sec)

Mysql>

re-login from MySQL , the new data on the master server is out.

mysql> show databases;

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

| Database |

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

| Information_schema |

| Fuck |

| MySQL |

| Test |

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

4 rows in Set (0.02 sec)

mysql> use fuck;

Database changed

mysql> select * from Fuck;

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

| ID | name |

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

| 1 | ABCD |

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

1 row in Set (0.00 sec)

Mysql>

MySQL Master-slave replication