MySQL database master-slave configuration

First, the primary database configuration
1) Modify the master database configuration file
[Mysqld]
#编号
Server-id=1
#打开二进制日志
Log-bin-master-bin
#主数据库日志
Log-bin-index=master-bin.index

2) Restart data
Service mysqld Restart

3) MySQL command to view the primary database log
SHOW MASTER STATUS

Second, from the database configuration
1) Modify the configuration file from the database
[Mysqld]
#编号
server-id=2
Relay-log-index=slave-relay-bin.index
Relay-log=slave-relay-bin

2) Restart the database
Another way:
/etc/init.d/mysql stop
/etc/init.d/mysql start

Third, the main library settings from the library connection is the user
1) Create user
Create user repl;
2) User Authorization
Grant REPLICATION SLAVE on * * to ' repl ' @ ' [from database IP] ' identified by ' [authorized user Password] ';
Flush privileges;

Iv. Modifying the point from the database to the primary database
1) Execute MySQL command
Change Master to master_host= ' [primary database IP] ', master_port=[Primary database Port],master_user= ' Repl ',
Master_password= ' [Repl's password] ', master_log_file= ' [master-bin.000001 (primary database binary log file)] ',
master_log_pos=0;

2) Start master/slave synchronization from Library
MySQL command: start slave;

3) View status from library
mysql command: show slave status \g;

4) Concou Stop master-Slave synchronization function
mysql command: Stop slave;

V. Import the local database into the remote database
1) Export SQL locally
Mysqldump-uroot-p TestDB > Testdb.sql
2) Copy SQL Guide remote server
SCP testdb.sql [Email protected][remote IP Address]:[remote folder path]
3) remote server Import SQL
Create DATABASE TestDB;
use;
source [SQL file address];

4) User-authorized SQL
Grant SELECT, INSERT, UPDATE, delete on *. * to ' [authorized user] ' @ '% '
Identified by ' [authorized user Password] ' with GRANT OPTION;

MySQL database master-slave configuration