MySQL Master-slave backup

2.7, Mysql master-slave backup
1. mysql Main structure:
Master:mysql master node, master receives an IO process request from Slave, reads the log information after the specified location of the log is read by the IO process that is responsible for the replication, and returns the IO process to slave. In addition to the information contained in the log, the returned information includes the location of the name of the Bin-log file that was returned to the master side already Bin-log
Slave:slave node, after the Slave IO process receives the information, the received log content is added to the end of the Relay-log file on the Slave side, and the read to the master side of the Bin-log file name and location are recorded in Master-info files so that the location of Masterbin-log can be clearly told at the next read
2. Server Preparation:
MySQL master server:
IP eth0:192.168.230.130
MySQL version: mysql-5.1.73
MySQL from the server:
IP eth0:192.168.230.128
MySQL version: mysql-5.1.73
3, two machines installed MySQL, master and slave operation

# useradd -M -s /sbin/nologin mysql# mkdir -p /data/mysql# chown -R mysql:mysql /data/mysql#  mv mysql-5.1.73-linux-i686-glibc23 /usr/local/mysql# cd /usr/local/mysql/# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql# cp support-files/my-large.cnf /etc/my.cnf# vim /etc/init.d/mysqld

basedir=/usr/local/mysqldatadir=/data/mysql

# chmod 755 /etc/init.d/mysqld# chkconfig mysqld on# vim /etc/profile.d/path.sh     ##加入环境变量

Export path= $PATH:/usr/local/mysql/bin

# source /etc/profile.d/path.sh # /etc/init.d/mysqld start# netstat -lnp | grep mysql

3. Create a Database
Primary server:

# mysql -uroot

mysql> CREATE DATABASE Smiledb;
Create and authorize only 192.168.230.128 (from IP) This IP to access the primary server 192.1268.230.130 above the database, and only the permissions of the database backup
mysql> grant replication Slave on . to ' rsync ' @ ' 192.168.230.128 ' identified by ' 123456 ' with GRANT option;
mysql> flush Privileges; # #刷新系统授权表
Mysql> flush tables with read lock; # #数据库只读锁定 to prevent data writes when the database is everywhere
Export the database smiledb and upload it to the slave server

# mysqldump -uroot smiledb > /root/smile.sql# scp /root/smile.sql 192.168.230.128:/root/

From the server:
Create a Smiledb database

# mysql -uroot

mysql> CREATE DATABASE Smiledb;
mysql> use Smiledb;
Mysql> Source/root/smile.sql; # #将拷贝过来的备份文件导入数据库

# mysql -ursync -h192.168.230.130 -p        ##测试从服务器上登录到主服务器

Primary server:
Configure MY.CNF file, remove comment blank line

# vim /etc/my.cnf

Server-id = 1 # #设置服务器ID, 1 indicates the primary server
Log-bin=mysql-bin # #启动MySQL日志系统
Binlog-do-db=smiledb # #白名单, need to synchronize the database, multiple libraries Repeat this parameter per library row
#binlog-ignore-db=mysql # #黑名单, unsynchronized databases, multiple libraries Repeat this parameter for each row of the library
#黑白名单二选一
Save exit
Restart MySQL

# /etc/init.d/mysqld restart

Enter the database

# mysql -uroot

Mysql> Show master status; # # with important values from the
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106 | Smiledb | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

From the server:
Configuring the My.cnf file, removing comments and blank lines

Server-id = 2 # # modified to 2, not added, expressed as from the database
Log-bin=mysql-bin # #启动MySQL日志系统
Binlog-do-db=smiledb # #白名单, need to synchronize the database, multiple libraries Repeat this parameter per library row
#binlog-ignore-db=mysql # #黑名单, unsynchronized databases, multiple libraries Repeat this parameter for each row of the library
#黑白名单二选一
Save exit
Restart MySQL

# /etc/init.d/mysqld restart# mysql -uroot

mysql> slave stop; # # Stop Slave synchronization process
Executing a synchronous statement
Mysql> Change Master to
-master_host= ' 192.168.230.130 ', # #主服务器地址
-master_user= ' rsync ', # #主服务器上创建的用来同步的用户
-master_password= ' 123456 ', # #用户密码
Master_log_file= ' mysql-bin.000002 ', #主服务器二进制文件 the kidney value obtained by Lord Show master status
master_log_pos=106; Kidney values obtained #主服务器二进制日志位置主上show master status
Turn on slave sync information
mysql> slave start;

Primary server:
Unlock the Library
mysql> unlock tables;

From the server:
Viewing synchronization information
Mysql> show Slave status\g; # #会显示很多
Slave_io_running:yes
Slave_sql_running:yes
The above two parameters are yes to indicate a successful configuration

Test:
Primary server:
mysql> use Smiledb;
Mysql> CREATE TABLE test (ID int not NULL primary key,name char (20));
Mysql> CREATE TABLE TB1 ( id int (4), name char (40));
Mysql> Show tables;
From the service:
mysql> use Smiledb;
Mysql> Show tables;
Whether to synchronize from the server two new tables with new table description synchronization succeeded

MySQL Master-slave backup