MySQL Master-Slave construction

First, the principle
MySQL master server records the various operations in the binary format into the Bin-log, from the connection master, and read the Bin-log, to achieve data synchronization
Backup: mysqldump-uroot-p 123456 db >1.sql
Import: mysql-uroot-p 123456 db < 1.sql

  Ii. preparatory work

mysqldump-uroot-s/tmp/mysql2.sock mysql > 123.sql;
mysql-uroot-s/tmp/mysql2.sock db1 < 123.sql 

Third, the configuration

1. Configure Master master server

(1) Locate the configuration file my.cnf, start the binary file.

Server-id=1 #给数据库服务的唯一标识, typically set to the end of the IP log-bin=mysql-bin #开启二进制日志

(2) Enter MySQL

Set root password: mysqladmin-uroot password ' 123456 ' into database: mysql-uroot-p123456

(3) Create an authorization account, give replication (copy) permission, from server IP to 105

GRANT REPLICATION SLAVE on * * to ' repl ' in ' 192.168.1.105 ' indentified by ' 123456 '

(4) View Log

Mysql>show Master status; +-------------------+----------+--------------+------------------+     | File | Position | binlog_do_db |     binlog_ignore_db | +-------------------+----------+--------------+------------------+     | master-bin.000001 |     1285 | | | +-------------------+----------+--------------+------------------+ 1 row in Set (0.00 sec)

(5) Restart MySQL service

2. Configure slave from the server

(1) Locate the configuration file my.cnf, modify the unique ID

server-id=2

(2) Restart MySQL service

(3) Connect Master

Change Master to master_host= ' 192.168.1.106 ', #master服务器的IP地址 master_port=3306, master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' master-bin.000001 ', #master服务器产生的日志 master_log_pos=0;

(4) Start slave

Start slave;

(5) Check the status from the library

show slave status\g; Slave_io_running=yes

This article is from the "Ming Linux blog" blog, be sure to keep this source http://zhaijunming5.blog.51cto.com/10668883/1690313

MySQL Master-Slave construction