The benefits of using MySQL master-slave replication are:
1, the use of master-slave server This architecture, stability can be improved. If the primary server fails, we can use the slave server to provide the service.
2, processing the user's request separately on the master-slave server, can improve the data processing efficiency.
3. Copy the data on the primary server to the slave server to protect the data from accidental loss.
Environment Description:
The new enterprise wants to build a MySQL database that is based on replication.
Primary server (mysql-master): IP address: 172.17.0.142
Slave Server (mysql-slave): IP address: 172.17.0.45
The master-slave server can provide the service normally.
to operate on the master server master:
1.[[email protected] ~]# vim/etc/my.cnf
Log-bin=mysql-bin//binary log must be enabled
Server-id=1//server ID
Comment out the Log-slave-updates
2.[[email protected] ~]# mysql-uroot-paixocm
[Email protected] (none) 03:04>grant replication Slave on * * to ' slave ' @ ' 172.17.0.45 ' identified by ' aixocm '; //---Establish an internal replication communication user
[Email protected] (none) 03:04>show master status;
+----------------------------+------------------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+----------------------------+------------------+--------------+------------------+
| mysql-bin.000006 |342| | |
+--------------- ------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
operation on slave from server
1.[[email protected] ~]# vim/etc/my.cnf
Log-bin=mysql-bin
server-id=2
Open the Log-slave-updates
[Email protected] (none) 00:17>stop slave;
Query OK, 0 rows Affected (0.00 sec)
[Email protected] (none) 00:17>change master to master_host= ' 172.17.0.142 ', master_user= ' slave ', master_password= ' aixocm ',master_log_file= ' mysql-bin.000006 ', master_log_pos=342;
Query OK, 0 rows affected (0.04 sec)
[Email protected] (none) 00:18>start slave;
Query OK, 0 rows Affected (0.00 sec)
[Email protected] (none) 00:19>show slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.17.0.142
Master_user:slave
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000006
read_master_log_pos:342
relay_log_file:localhost-relay-bin.000002
relay_log_pos:251
relay_master_log_file:mysql-bin.000006
slave_io_running:yes-------must be Yes
Slave_sql_running:yes-------must be Yes
Test:
1.master above operation new Database new table
[Email protected] (none) 03:11>create database sx;
Query OK, 1 row affected (0.02 sec)
[Email protected] (none) 03:11>use SX;
Database changed
[Email protected] 03:12>create table SX (
name int);
Query OK, 0 rows affected (0.03 sec)
2.slave above check whether synchronization
[Email protected] (none) 00:21>show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| SX|
| Test |
|
+--------------------+
6 rows in Set (0.00 sec)
[Email protected] (none) 00:22>use SX;
Database changed
[email protected] 00:23>show tables;
+--------------+
| TABLES_IN_SX |
+--------------+
| SX|
+--------------+
1 row in Set (0.00 sec)
display effect as above-----congratulations on your experiment.
Warm tips:
1. Firewalls
2.selinux
3. The network does not pass through
4.pos No
5. Configuration Error
6. After changing the configuration or other actions: Slave stop and then slave start
MySQL master-slave copy--fool-type operation
