MySQL Master-slave replication

The principle of MySQL master-slave replication:

The binary log file of the main library is read from the library at a certain frequency, and the same operation is performed from the library according to the binary records of the main library, in order to achieve the effect of synchronizing the contents from the library with the main library.

MySQL How to install I will not say more, we can refer to:

http://alipay.blog.51cto.com/7119970/1570454

An error occurred during the installation process:

http://alipay.blog.51cto.com/7119970/1571229

This article creates a reference article for the data sheet:

http://alipay.blog.51cto.com/7119970/1570488

Primary server: 192.168.159.130

From server: 192.168.159.131

First, the primary server configuration:

[[email protected] ~]# service iptables stop;

Iptables:setting chains to Policy Accept:filter [OK]

iptables:flushing firewall rules: [OK]

iptables:unloading modules: [OK]

[[email protected] ~]# /etc/rc.d/init.d/mysql Stop

Shutting down MySQL. [OK]

[Email protected] ~]# vi/etc/my.cnf

[Mysqld]

Log_bin = Mysql-bin #必须开启二进制日志

server_id = 1 #服务器id号, must be a unique number.

[[email protected] ~]# /etc/rc.d/init.d/mysql start

Starting MySQL .... [OK]

[Email protected] ~]# mysql-uroot-p

mysql> Create user [email protected] ' localhost ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

mysql> Grant Replication Slave on * * to [e-mail protected] ' 192.168.159.131 ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000001 |              583 |                  |                   | |

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

1 row in Set (0.00 sec)

Second, from the server configuration

[[email protected] ~]# /etc/rc.d/init.d/mysql Stop

Shutting down MySQL. [OK]

[Email protected] ~]# vi/etc/my.cnf

[Mysqld]

Log_bin = Mysql-bin #必须开启二进制日志

server_id = 2 #确保这个id号没有被其他服务器使用

[[email protected] ~]# /etc/rc.d/init.d/mysql start

Starting MySQL .... [OK]

[Email protected] ~]# mysql-uroot-p

Enter Password:

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

Your MySQL Connection ID is 1

Server Version:5.6.16-log Source Distribution

Copyright (c), the Oracle and/or 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= ' 192.168.159.130 ', master_user= ' gupt11 ', master_password= ' 123456 ', master_ Log_file= ' mysql-bin.000001 ', master_log_pos=583;

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

# #Slave_IO_Running and slave_sql_running must be yes, see below:

Mysql> show slave status\g;

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

Slave_io_state:waiting for Master to send event

master_host:192.168.159.130

Master_user:gupt11

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:583

relay_log_file:gupt11-relay-bin.000002

relay_log_pos:283

relay_master_log_file:mysql-bin.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:583

relay_log_space:457

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

Master_uuid:5c77474b-63d6-11e4-9337-000c2941ae03

Master_info_file:/usr/local/mysql/data/master.info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:

Executed_gtid_set:

auto_position:0

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>

Third, in the master server test

mysql> CREATE database Scott;

Query OK, 1 row Affected (0.00 sec)

mysql> use Scott

Database changed

mysql> CREATE TABLE Dept

(

-Deptno INT PRIMARY KEY,

Dname VARCHAR (14),

Loc VARCHAR (13)

);

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT into Dept VALUES

(Ten, ' ACCOUNTING ', ' NEW YORK ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT into Dept VALUES

(+, ' DALLAS ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into Dept VALUES

(+, ' SALES ', ' CHICAGO ');

Query OK, 1 row affected (0.01 sec)

mysql> INSERT into Dept VALUES

(+, ' OPERATIONS ', ' BOSTON ');

Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from dept;

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

| Deptno | Dname | Loc |

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

| 10 | ACCOUNTING | NEW YORK |

| 20 | | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

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

4 rows in Set (0.00 sec)

Mysql>

Iv. viewing from the server

Mysql> show databases;

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

| Database |

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

| Information_schema |

| MySQL |

| Performance_schema |

| Scott |

| Test |

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

5 rows in Set (0.00 sec)

mysql> use Scott;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> SELECT * from dept;

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

| Deptno | Dname | Loc |

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

| 10 | ACCOUNTING | NEW YORK |

| 20 | | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

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

4 rows in Set (0.00 sec)

Mysql>

This article is from the "Xbc's homepage" blog, so be sure to keep this source http://alipay.blog.51cto.com/7119970/1571244

MySQL Master-slave replication