MySQL Master-slave replication

Server multi-MySQL service setup and master-slave replication

One, One_server multi-mysql service building

"1" The first MySQL service build

No introduction here, we think the first MySQL service has been completed;

"2" copy of MySQL Basedir directory

# cp/usr/local/mysql//usr/local/mysql_s   //If the original MySQL basedir is/usr/local/myql/

"3" Copy configuration file

# cp/usr/local/mysql_s/support_files/my-small.cnf/usr/local/mysql_s/my.cnf
# VIM/USR/LOCAL/MYSQL_S/MY.CNF
    [mysqld]
    port = 3307    //original MySQL port = 3306
    socket =/tmp/mysql_s.sock    //original MySQL socket in/ Tmp/mysql.sock

"4" Copy startup script and modify

# cp/etc/init.d/mysqld/etc/init.d/mysqld_s
# vim/etc/init.d/mysqld_s
    basedir=/usr/local/mysql_s 
    datadir=/data/mysql_s//    original MySQL datadir=/data/mysql
    conf= $basedir/my.cnf    //In the above two add conf path, It was copied to Basedir.

"5" Initialization

# cd/usr/local/mysql_s
#/scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql_s--datadir=/data/ mysql_s

"6" starts a second MySQL service

#/etc/init.d/mysqld_s Start
# PS grep |grep mysqld  //You should see data for two groups of MySQL services

"7" for login issues with two MySQL services

Login Two MySQL, can be via port:

# mysql-h127.0.0.1-p3306    //Large p specify port, login another with-p3307;h to specify host, because is host, with 127.0.0.1

You can also use the socket:

# mysql-s/tmp/mysql.sock   //Login Another time, with-s/tmp/mysql_s.sock

Second, MySQL master-slave construction

Note: 1. mysql's master-slave replication is fragile, very easy to interrupt, it is best to set monitoring, slave_mysql slave_io_running:yes,slave_sql_running:yes monitoring, to ensure that all is yes.

2. MySQL master-slave replication, the operator can only be Master_mysql,slave_mysql side operation will be error.

Here is a demonstration of two servers, two MySQL service master-slave copy building method. If mster and slave server MySQL installation configuration exactly the same.

master_ip:192.168.220.120, slave_ip:192.168.220.130

"1" Modify the configuration of the Master_mysql:

# vim/etc/my.cnf
    [mysqld]
    Server-id  = 1   //Server-id of the primary MySQL service cannot be the same as from the server
    Log-bin=lius   // Open binary log, custom after equals sign, lius.000001 binary log appears under DataDir
    binlog-ignore-db = mysql  //can also be binlog-do-db=db1,db2, Specifying multiple synchronization databases, specifying a library that ignores synchronization
#/etc/init.d/mysqld Restart

"2" Login Master_mysql, do the following:

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.220.130 ' identified by ' xiaobieniu7788 ';  Authorized user, specify slave IP, give replication permission only.
mysql> flush Privileges;    Refresh permissions
mysql> flush tables with read lock;      Lock table, determine the same data
mysql> show master status;     Display values for later configuration from server useful
+-------------+----------+--------------+------------------+
| File        | Position | binlog_do_db | binlog_ignore_db |
+-------------+----------+--------------+------------------+
| lius.000002 |      337 |              | MySQL            |
+-------------+----------+--------------+------------------+

"3" Backs up master_mysql data so that it can be restored to slave_mysql to achieve consistent data:

# mysqldump--all-databases--ignore-tables=mysql.*--default-character-set=utf8 > All.sql   //Ignore MySQL library synchronization, Specifies that the default character set is UTF8

"4" Slave_mysql Configuration parameter modification:

# vim/etc/my.cnf
    server-id=2   //Ensure that the primary server is not the same
    replicate-ignore-db=mysql   //Because the master has been specified, from the optional configuration, Can also be written in replicate-do-db=db1,db2 form
#/etc/init.d/mysqld Restart   //Restart service, Log-bin from the server can not be opened
# MySQL--default-character-set=utf8 < all.sql   //Restore primary server backup files, files because cross-server, can use SCP or lrzsz the function of this package to implement

"5" Login Slave_mysql, do the following:

mysql> slave stop;  Stop slave

Master_log_file= ' lius.000002 ', master_log_pos=337;
Specify the IP of the primary server, specify the primary MySQL port, specify the user name and password to log in to the master server, and specify two important data for mysql> show master status above, which can be compared with the above table;
Then, you also need to go to the main MySQL login screen, unlock the read lock, because before adding mysql> flush tables With read lock, now need to mysql> unlock tables, and then go back from MySQL to do the following:
mysql> slave start;

"6" Tests whether master-slave replication was successful:

Mysql> Show slave status; Get the following two items are all yes, that is, the master-slave replication configuration is successful, in the Master_mysql login interface, to build tables, delete the table and other operations, in slave_mysql see whether the results are synchronized, check the master-slave effect.

Slave_io_running:yes
Slave_sql_running:yes

MySQL Master-slave replication