MySQL master-slave synchronization + mysql-zrm backup

MySQL master-slave synchronization is a mature architecture: advantages:
① The query can be performed on the slave server (that is, the READ function we often call) to reduce the pressure on the master server.
② Back up data from the master server to avoid affecting the services of the master server during the backup.
③ When a problem occurs on the master server, you can switch to the slave server.
I. experiment environment
1. IP address and Host Name
192.168.10.51 db1.bkjia.com master
192.168.10.52 db2.bkjia.com slave
2. Required Software
Mysql-5.1.63.tar.gz
3. Install gcc and corresponding dependent packages
[Root @ db1 ~] # Yum-y install gcc ncurses-devel
4. Database directory and others
My. cnf configuration file/usr/local/mysql/my. cnf
Mysql database location/usr/local/mysql/data/
Socket location/usr/local/mysql/tmp/mysql. sock
Ii. Master Configuration
1. Install Mysql
[Root @ db1 ~] # Tar-zxvf mysql-5.1.63.tar.gz-C/usr/src/
[Root @ db1 ~] # Useradd-M-s/sbin/nologin mysql
[Root @ db1 ~] # Cd/usr/src/mysql-5.1.63/
[Root @ db1 mysql-5.1.63] # vim configure # comment out the following lines
52297 # $ RM "$ your file"
[Root @ db1 mysql-5.1.63] #. /configure -- prefix =/usr/local/mysql/-- enable-validator -- with-extra-charsets = complex -- enable-thread-safe-client -- with-big-tables -- -readline -- with-ssl -- with-embedded-server -- enable-local-infile -- with-plugins = innobase -- with-mysqld-user = mysql
[Root @ db1 mysql-5.1.63] # make
[Root @ db1 mysql-5.1.63] # make install
2. Configure Mysql
[Root @ db1 mysql-5.1.63] # cp support-files/my-medium.cnf/usr/local/mysql/my. cnf
[Root @ db1 mysql-5.1.63] #/usr/local/mysql/bin/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql/-- datadir =/usr/local/mysql /data
[Root @ db1 mysql-5.1.63] # chown-R root. mysql/usr/local/mysql
[Root @ db1 mysql-5.1.63] # chown-R mysql/usr/local/mysql/data/
[Root @ db1 mysql-5.1.63] # echo "/usr/local/mysql/lib/mysql">/etc/ld. so. conf
[Root @ db1 mysql-5.1.63] # ldconfig
[Root @ db1 mysql-5.1.63] # echo "export PATH =/usr/local/mysql/bin: $ PATH">/etc/profile
[Root @ db1 mysql-5.1.63] # source/etc/profile
[Root @ db1 mysql-5.1.63] # mkdir/usr/local/mysql/tmp
[Root @ db1 mysql-5.1.63] # chmod 777/usr/local/mysql/tmp/
[Root @ db1 mysql-5.1.63] # vim/usr/local/mysql/my. cnf # modify the socket location
Socket =/usr/local/mysql/tmp/mysql. sock
[Root @ db1 mysql-5.1.63] # mysqld_safe -- defaults-file =/usr/local/mysql/my. cnf &
[Root @ db1 mysql-5.1.63] # mysql-uroot-p -- socket =/usr/local/mysql/tmp/mysql. sock
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 5
Server version: 5.1.63-log Source distribution

Copyright (c) 2000,201 1, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> grant replication slave on *. * to 'backup '@ '192. 168.10.52 'identified by 'backupped ';
Query OK, 0 rows affected (0.00 sec)

Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000002 | 265 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
3. Package the master database for data migration
[Root @ db1 mysql-5.1.63] # cd/usr/local/mysql/
[Root @ db1 mysql] # tar-zcf data0708.tar.gz./data/
Iii. Slave Configuration
The installation process is as follows ......
[Root @ db2 mysql-5.1.63] # vim/usr/local/mysql/my. cnf # modify server-id
Server-id = 2
Copy the backup data of the primary database and decompress it to the corresponding directory.
[Root @ db2 mysql-5.1.63] # mysqld_safe -- defaults-file =/usr/local/mysql/my. cnf &
[Root @ db2 mysql-5.1.63] # mysql-uroot-p -- socket =/usr/local/mysql/tmp/mysql. sock
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 2
Server version: 5.1.63-log Source distribution

Copyright (c) 2000,201 1, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql> change master to master_host = '192. 168.10.51 ', master_user = 'backup', master_password = 'backuppwd ', master_log_file = 'mysql-bin.000002', master_log_pos = 192;
Query OK, 0 rows affected (0.24 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

Mysql> show slave status \ G
......
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
......
Iv. Verification
1. operate on the Master
[Root @ db1 mysql-5.1.63] # mysql-uroot-p -- socket =/usr/local/mysql/tmp/mysql. sock
Mysql> create database mysqltest;
Query OK, 1 row affected (0.00 sec)

Mysql> use mysqltest;
Database changed
Mysql> create table user (id int (5), name char (10 ));
Query OK, 0 rows affected (0.00 sec)

Mysql> insert into user values (0001, 'xieping ');
Query OK, 1 row affected (0.00 sec)

Mysql> insert into user values (0002, 'huxinxin ');
Query OK, 1 row affected (0.00 sec)

Mysql> insert into user values (0003, 'dingpeng ');
Query OK, 1 row affected (0.00 sec)
2. Operate on Slave
[Root @ db2 mysql-5.1.63] # mysql-uroot-p -- socket =/usr/local/mysql/tmp/mysql. sock
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Mysqltest |
| Test |
+ -------------------- +
4 rows in set (0.02 sec)

Mysql> select id, name from mysqltest. user;
+ ------ + ---------- +
| Id | name |
+ ------ + ---------- +
| 1 | xieping |
| 2 | huxinxin |
| 3 | dingpeng |
+ ------ + ---------- +
3 rows in set (0.00 sec)