mysql zrm_MySQL的主从同步+mysql-zrm备份

MySQL的主从同步是一个很成熟的架构：优点为：①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力②在从主服务

MySQL的主从同步是一个很成熟的架构：优点为：

①在从服务器可以执行查询工作(即我们常说的读功能)，，降低主服务器压力

②在从主服务器进行备份，避免备份期间影响主服务器服务

③当主服务器出现问题时，可以切换到从服务器

一、实验环境

1、IP与主机名

192.168.10.51 db1.linuxidc.com master

192.168.10.52 db2.linuxidc.com slave

2、所需软件

mysql-5.1.63.tar.gz

3、安装gcc和相应的依赖包

[root@db1 ~]# yum -y install gcc ncurses-devel

4、数据库目录及其它

my.cnf配置文件 /usr/local/mysql/my.cnf

mysql数据库位置 /usr/local/mysql/data/

socket位置 /usr/local/mysql/tmp/mysql.sock

二、Master配置

1、安装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 #把下面行注释掉

52297 # $RM "$cfgfile"

[root@db1 mysql-5.1.63]# ./configure --prefix=/usr/local/mysql/ --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-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、配置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 #修改socket位置

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, 2011, 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 'backuppwd';

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、打包主库迁移数据

[root@db1 mysql-5.1.63]# cd /usr/local/mysql/

[root@db1 mysql]# tar -zcf data0708.tar.gz ./data/

三、Slave配置

安装过程如上，步骤省略......

[root@db2 mysql-5.1.63]# vim /usr/local/mysql/my.cnf #修改server-id

server-id = 2

将主数据库备份数据拷贝过来并解压到相应的目录

[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, 2011, 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=265;

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

......

四、验证

1、在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、在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)

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网