Mysql Master-slave replication (Master-slave) actual operation case _mysql

In this chapter, we will learn how to do user authorization and master-slave replication in Mysql

Here's a look at the benefits of Mysql master-slave replication:

1. If there is a problem with the primary server, you can quickly switch to the service provided from the server
2, can perform query operation from the server, reduce the access pressure of the primary server
3. You can perform backups from the server to avoid the services that affect the primary server during backup
Note that only infrequently updated data or low real-time requirements of data can be queried from the server, real-time requirements of high data still need to be obtained from the main database

Here we first have to complete the user authorization, in order to give sufficient permissions from the server to remote login to the master server Mysql

In this case, I assume
The IP of the primary server is: 192.168.10.1
IP from server: 192.168.10.2

Mysql Grant User authorization

View the Mysql user table

Copy Code code as follows:

Msyql> mysql-uroot-p123123;
Msyql> Select User, host, password from Mysql.user;

The results are as follows:

Copy Code code as follows:

+------------------+-----------+-------------------------------------------+
| user | Host | password |
+------------------+-----------+-------------------------------------------+
| Root | localhost | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 |
| Root | 127.0.0.1 | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 |
+------------------+-----------+-------------------------------------------+

As seen from the table above, the root user can only log in to Mysql from this computer, which is from localhost or 127.0.0.1

Now to add an authorized user through the grant command

Copy Code code as follows:

Msyql>? Grant//View detailed usage of grant

Msyql> Grant all on *.* to user1@192.168.10.2 identified by "123456"; *.* = ALL databases. All tables
Or
msyql> grant replication Slave on *.* to ' user2 ' @ ' 192.168.10.% ' identified by ' 123456 '; % to represent wildcard characters

The grant command gave the user User1 permissions from 192.168.10.2, allowing them to log on remotely, as follows:

Copy Code code as follows:

+------------------+--------------+-------------------------------------------+
| user | Host | password |
+------------------+--------------+-------------------------------------------+
| Root | localhost | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 |
| Root | 127.0.0.1 | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 |
| User1 | 192.168.10.2 | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
| User2 | 192.168.10.% | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |
+------------------+--------------+-------------------------------------------+

At this point, you can access the 10.1 Mysql on the 192.168.10.2 machine, as follows:

Copy Code code as follows:

Msyql> mysql-uuser1-p123456-h192.168.10.1;

Mysql Bin-log Log

Opens the Bin-log binary log, which saves all additions and deletions to facilitate data recovery or synchronization

To modify the master server MySQL configuration file:

Copy Code code as follows:

shawn@shawn:~$ sudo vi/etc/mysql/my.cnf;

/********** my.cnf **********/
[Mysqld]

#开启慢查询日志, log queries that are too long for SQL statements to optimize
Log_slow_queries =/var/log/mysql/mysql-slow.log

#开启 Bin-log Log
Log-bin =/var/log/msyql/mysql-bin.log

Restart the Mysql service after the addition completes

Copy Code code as follows:

shawn@shawn:~$ sudo/etc/init.d/mysql Restart

Now you can see if the Bin-log log is open successfully with the following command

Copy Code code as follows:

Mysql> Show variables like "%log_%";

| Log_bin | On |
| log_slow_queries | On |

If on is displayed, you can see the mysql-bin.000001 binaries in the/var/log/mysql/folder

Related actions on the Bin-log log:

Copy Code code as follows:

mysql> flush logs;

There will be more than one new Bin-log log at this time.

Copy Code code as follows:

Mysql> Show master status;

View the last Bin-log log as follows:

Copy Code code as follows:

+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000002 |              107 |                  | |
+------------------+----------+--------------+------------------+

Mysql> show master logs;

View all Bin-log logs as follows:

Copy Code code as follows:

+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
|      mysql-bin.000001 | 4340 |
|       mysql-bin.000002 | 107 |
+------------------+-----------+

mysql> Reset Master;

Empty all Bin-log logs

Copy Code code as follows:

shawn@shawn:~$ mysqlbinlog/var/log/mysql/mysql-bin.000001 | More

View Bin-log Log Contents

Copy Code code as follows:

#如果有字符集问题的话可以执行:
shawn@shawn:~$ Mysqlbinlog--no-defaults/var/log/mysql/mysql-bin.000001

shawn@shawn:~$ mysqlbinlog/var/log/mysql/mysql-bin.000002 | Mysql-uroot-p123123 test;
Restore all operations in the mysql-bin.000002 to the test database

shawn@shawn:~$ mysqlbinlog/var/log/mysql/mysql-bin.000002--start-position= "193"--stop-position= "398" | Mysql-uroot-p123123 test;
Restore the action specified in mysql-bin.000002 (position) to the test database

Mysql Master-slave replication-Data synchronization

At this point, first ensure that the MySQL user authorization has been completed and that the MySQL Bin-log log has been successfully opened
And make sure that the Server-id of each server is unique

To modify the MySQL profile of the primary server (192.168.10.1) again:

Copy Code code as follows:

shawn@shawn:~$ sudo vi/etc/mysql/my.cnf;

/********** my.cnf **********/
#取消 Server-id Annotation Symbol
Server-id = 1
/****************************/

#重启 Mysql Service
shawn@shawn:~$ sudo/etc/init.d/mysql Restart

Here, the master server configuration is complete, very simple

This time we mainly do is to synchronize the primary server data from the server, synchronization is all the future additions and deletions to the main service, but the existing master server in a large number of data to be synchronized to the from the server, the operation is as follows:

Copy Code code as follows:

#清空一下主服务器的 bin-log Log, (optional: Insurance operation, prevent master-slave bin-log log confusion)
mysql> Reset Master;

#然后备份导出主服务器中现有的 Test Database
shawn@shawn:~$ mysqldump-uroot-p123123 test-l-f >/tmp/test.sql;

-F = flush logs, generating new log files, including Bin-log logs
-L = Lock database to prevent data from being written when it is exported and automatically unlocked after completion

#完成后把文件传输给从服务器
shawn@shawn:~$ Scp/tmp/test.sql 192.168.10.2:/tmp/

#然后再查询确保一下从服务器已经成功授过权
Mysql> Show grants for USER1@192.168.10.2\G

1. Row ***************************
Grants for user1@192.168.10.2:
GRANT all privileges in *.* to ' user1 ' @ ' 192.168.10.2 '
Identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 '

Once we're done, we're going to import the existing data from the server (192.168.10.2):

Copy Code code as follows:

#清空一下从服务器的 bin-log Log, (optional: Insurance operation)
mysql> Reset Master;

#然后导入主服务器中现有的数据
shawn@shawn:~$ mysqldump-uroot-p123123 test-v-F </tmp/test.sql;

-v = view Import Details
-F = is when the middle encounters an error, you can skip the past, continue to execute the following statement
Of course, you can also use the source command to import
OK, so far the primary server (192.168.10.1) and the existing data from the server (192.168.10.2) have been successfully manually synchronized

Next, modify the MySQL configuration file from the server (192.168.10.2):

Copy Code code as follows:

shawn@shawn:~$ sudo vi/etc/mysql/my.cnf;

/********** my.cnf **********/
#取消 Server-id annotation Symbol and modify the value
Server-id = 2

#取消 master-host annotation Symbol and modify the value
Master-host = 192.168.10.1

#取消 master-user annotation Symbol and modify the value
Master-user = User1

#取消 Master-password annotation Symbol and modify the value
Master-password = 123456

#取消 the Master-port annotation symbol and modify the value, the primary server default port number is: 3306
Master-port = 3306
/****************************/

#重启 Mysql Service
shawn@shawn:~$ sudo/etc/init.d/mysql Restart

Profile modification completed, login to Mysql from the server instead of the remote login server (192.168.10.1)

Copy Code code as follows:

#在从服务器中登入自身的 Mysql
Msyql> mysql-uroot-p123123;

#查看是否已经取得同步
Msyql> Show Slave Status\g

1. Row ***************************
Connect_retry:60
master_log_file:mysql-bin.000002
read_master_log_pos:106
Slave_io_running:yes
Slave_sql_running:yes

slave_io_running If yes, the representative successfully synchronizes from the master server to the Bin-log log
Slave_sql_running If yes, represents the successful execution of the SQL statement in the Bin-log log
The value of the Master_log_file and read_master_log_pos at this time should correspond to the value of the show Master Status command on the primary server
The 60 delegates in Connect_retry sync Bin-log log every 60 seconds on the main server.

OK, if you see the two key yes, then you can go to test, insert new data in the primary server, then go to view from the server, if not unexpectedly, you will be excited, the data has been synchronized

Here are some other frequently used commands:

Copy Code code as follows:

#启动复制线程
msyql> start Slave

#停止复制线程
msyql> Stop Slave

#动态改变到主服务器的配置
Msyql> Change Master to

#查看从数据库运行进程
Msyql> Show Processlist

Also, here are some common errors in the operation:

Problem: Unable to sync from database
Slave_sql_running value is NO, or seconds_bebind_master value is Null

Reason:
First, the program may be written on the slave
Second, it may be slave machine restart, transaction rollback caused by

Workaround One:

Copy Code code as follows:

msyql> stop Slave;

Msyql> set GLOBAL sql_slave_skip_counter=1;

msyql> start slave;

Workaround Two:

Copy Code code as follows:

msyql> stop Slave;

#查看主服务器上当前的 bin-log log name and offset
Msyql> Show master status;

#获取到如下内容:
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000005 |              286 |                  | |
+------------------+----------+--------------+------------------+

#然后到从服务器上执行手动同步
Msyql> Change Master to
-> master_host= "192.168.10.1"
-> master_user= "user1"
-> master_password= "123456"
-> master_post=3306
-> master_log_file= "mysql-bin.000005"
-> master_log_pos=286;

msyql> start slave;

Again through show slave status View:
If the value of slave_sql_running changes to Yes, the Seconds_bebind_master value is 0 o'clock, which is normal

Well, as above is my own in the operation of the summary of some of the content, if there are better suggestions, welcome to discuss with the message
By the way, I'm using Ubuntu 12.04.