MySQL master-slave synchronization + mysql-zrm backup

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.