MySQL master-slave replication steps

Master End 192.168.200.77

1. Add in/ETC/MY.CNF

[Mysqld]

Log-bin=mysql-bin

Server-id=1

2. Check that the settings are in effect

First, whether there are mysql-bin.000001 files and mysql-bin.index files in the data directory

Second, mysql> show variables like '%log_bin% ';

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

| variable_name | Value |

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

| Log_bin | On |

| Log_bin_basename | /application/mysql/data/mysql_log_bin |

| Log_bin_index | /application/mysql/data/mysql_log_bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| Sql_log_bin | On |

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

6 rows in Set (0.02 sec)

3. Add MySQL user, to slave connection use

mysql> grant replication Slave on * * to ' rep ' @ ' percent ' identified by ' oldboy123 ';

mysql> flush Privileges;

4. Backing up the main library database

1.flush table with read lock;

2. Open a different window

Mysqldump-uroot-poldboy-a-B--master-data=2 >/tmp/mysql_all_b.sql

can view bin-log information

Mysql>show Master status;

Mysql>show master logs;

3.unlock tables;

#########################################################################

Salve End 192.168.200.88

1. Modify/ETC/MY.CNF to add

[Mysqld]

server-id=2

2.mysql-uroot-poldboy </tmp/mysql_all_b.sql

3.change Master to

Master_host= ' 192.168.200.77 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' oldboy123 ',

Master_log_file= ' mysql_log_bin.000035 ',

master_log_pos=408;

View Cat/application/mysql/data/master.info

4.start slave;

show slave status\g;

Slave_io_running:yes

Slave_sql_running:yes

With 2 Yes, that means it's successful.

Insert the record on the master side and see if there is any synchronization on the slave side.

Note:

--master-data=1 for restore from library, change master statement, use change master on slave side

You can not add Master_log_file,master_log_pos statements

The--master-data=2 Change Master statement is commented

MySQL master-slave replication steps