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