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

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 distributionCopyright （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 distributionCopyright （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 statusG
......
             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）phpMyAdmin的mcrypt错误MySQL 从 5.1.26-rc 升级到 5.5.15相关资讯 MySQL主从同步

Ubuntu下MySQL主从同步配置（今 14:59）
MySQL主从同步校验与重新同步（09月01日）
监控MySQL主从同步延时（08月03日）

MySQL生产库主从重新同步操作注意（09月01日）
Linux下MySQL主从同步配置（08月23日）
Linux下MySQL数据库主从同步配置（03月11日）

本文评论查看全部评论（0）

评论声明