MySQL是目前轻量级数据库MySQL的冗余方案之首选
以下是MySQL的异步主从复制的配置过程
先配置系统yum，此处不已源码包的安装过程为例来介绍了!
也不再介绍yum的配置过程，从安装mysql开始
此处用两个主机安装
master：192.168.0.111
slave：192.168.0.222
安装MySQL（主从两个节点上都安装）
yum -y install mysql mysql-server
创建mysql的数据目录（两个节点）
mkdir /opt/mysql_data && chown -R mysql.mysql /opt/mysql_data && chmod 766 /opt/mysql_data
配置mysqld（master节点）
编辑/etc/my.cnf内容如下，先启动mysql的（两个节点）：
[mysqld]
character-set-server = utf8
datadir=/opt/mysql_data
socket=/tmp/mysql.sock
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
启动mysqld：
service mysqld start && chkconfig --level 35 mysqld on
************************************************
注意：此处以主从复制为主题不讨论iptables
和SELinux，所以事先关闭SELinux和iptables
否则mysqld会起不来
************************************************
创建要同步的数据库和同步的用户（在master节点）
mysql> grant replication slave,reload,super on *.* to cai@192.168.0.222 identified by 'cai'; //创建cai用户
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to cai@192.168.0.222 identified by 'cai';
Query OK, 0 rows affected (0.00 sec)
mysql> create database caidb; //创建要同步的数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; select user,host from mysql.user; //查看创建的库和用户信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| caidb |
| mysql |
| test |
+--------------------+
4 rows in set (0.01 sec)
+------+---------------+
| user | host |
+------+---------------+
| root | 127.0.0.1 |
| cai | 192.168.0.222 |
| | localhost |
| root | localhost |
| | master |
| root | master |
+------+---------------+
6 rows in set (0.00 sec)
在slave节点上尝试连接master，测试网络和用户的可用性：
[root@slave ~]# mysql -ucai -pcai -h 192.168.0.111 -e "select user,host from mysql.user;show databases;" //用cai登录远程数据库192.168.0.111，执行sql “show databases”
+------+---------------+
| user | host |
+------+---------------+
| root | 127.0.0.1 |
| cai | 192.168.0.222 |
| | localhost |
| root | localhost |
| | master |
| root | master |
+------+---------------+
+--------------------+
| Database |
+--------------------+
| information_schema |
| caidb |
| mysql |
| test |
+--------------------+
在要同步的数据库中创建表和模拟数据（在master）
mysql> create table `caitable` (`id` int(5) NOT NULL auto_increment, `name` varchar(32) NOT NULL, `password` char(32) NOT NULL, PRIMARY KEY (`id`)); //创建表
插入模拟数据用一下脚本：
[root@master ~]# cat ist.sh
#!/bin/bash
for ((i=1; i<=10; i++)) //循环插入10条数据
do
mysql -uroot -e "insert into caidb.caitable(id,name,password) value ('$i','user$i',md5('usercai$i'));"
done
mysql -uroot -e "select * from caidb.caitable;" //查询插入的数据
然后到处master的数据导入slave的数据库中
导出（在mater）：
[root@master ~]# mysqldump -uroot caidb > caidb.sql
[root@master ~]# scp caidb.sql 192.168.0.222:~/
root@192.168.0.222's password:
caidb.sql
*************************************************************************************
注意：此处执行scp如果某个节点报bash: scp: command not found
就执行yum -y install openssh-clients
*************************************************************************************
然后在slave上创建数据库并导入数据
mysql> create database caidb; //创建slave的数据库
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
[root@slave ~]# mysql -uroot caidb<caidb.sql //导入从master上到处的数据
[root@slave ~]# mysql -uroot -e "select * from caidb.caitable;" //查看数据是否导入
+----+--------+----------------------------------+
| id | name | password |
+----+--------+----------------------------------+
| 1 | user1 | 848bbbf06aeba9495a47578569cee381 |
| 2 | user2 | 4b1700e322bf3619376ef6df33d4a68d |
| 3 | user3 | 6238fe4a6ae91fb4e4c364dab46f3570 |
| 4 | user4 | e695fa61724b69ef58b3ff38d6d26322 |
| 5 | user5 | f0bab2bf65014192d9bc6e139ba5d520 |
| 6 | user6 | be56605958ccaabfa9eeb6d5d33dd075 |
| 7 | user7 | fec2992b831f761e61c1f406f1c0e7ec |
| 8 | user8 | 4ab3f2d0a35ca849e86ddc9a7856500a |
| 9 | user9 | 77998b564bf7cd7db0df7b57538e93f8 |
| 10 | user10 | d7d61d43582dbae4395dc298bc8c6d80 |
+----+--------+----------------------------------+
配置master的my.conf文件，在mysqld部分加入一下内容：
server-id = 1 //server id
log_bin = /var/log/mysql/mysql-bin.log //binlog的位置
binlog_do_db = high //要同步的数据库
binlog_ignore_db = mysql //忽略不同步的数据库
然后重启master的mysqld
*********************************************************************
注意：此处配置的时候最好别加注释，因为本人写c程序成惯性了
注释一般用“//”比较顺手，所以请加入配置文件的时候去掉注释或者
改用“#”作为注释符
**********************************************************************
配置slave，在my.cnf的mysqld加入如下内容：
server-id = 2 //server id不可以重复
master-host = 192.168.0.111 //master的ip地址
master-user = cai //用这个用户获取binlog
master-password = cai //用户密码
重启slave的mysqld
将获得master的状态（在master上）
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | caidb | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记住file字段的binlog的名字
首次手动同步slave的slave复制（在slave上）
mysql> slave stop; //停止slave
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.0.111',master_user='cai',master_password='cai',master_log_file='mysql-bin.000001',master_log_pos=106; //master_log_file是上述查看master状态里的File字段，master_log_pos是查看马赛特如状态里的Position字段，注意：master_log_post后边的值不加单引号
Query OK, 0 rows affected (0.03 sec)
mysql> slave start; //启动slave
Query OK, 0 rows affected (0.00 sec)
在slave节点上执行如下语句，如果返回的众多值里，这两个值的状态如下，则说明已经配置成功了：
[root@slave ~]# mysql -uroot -e "show slave status"
Slave_IO_Running Yes
Slave_SQL_Running Yes
#########################################################
测试主从复制
首先在master上更改caidb中的东西如下：
mysql> select * from caitable; select * from caitest;
+----+--------+----------------------------------+
| id | name | password |
+----+--------+----------------------------------+
| 1 | user1 | 848bbbf06aeba9495a47578569cee381 |
| 2 | user2 | 4b1700e322bf3619376ef6df33d4a68d |
| 3 | user3 | 6238fe4a6ae91fb4e4c364dab46f3570 |
| 4 | user4 | e695fa61724b69ef58b3ff38d6d26322 |
| 5 | user5 | f0bab2bf65014192d9bc6e139ba5d520 |
| 6 | user6 | be56605958ccaabfa9eeb6d5d33dd075 |
| 7 | user7 | fec2992b831f761e61c1f406f1c0e7ec |
| 8 | user8 | 4ab3f2d0a35ca849e86ddc9a7856500a |
| 9 | user9 | 77998b564bf7cd7db0df7b57538e93f8 |
| 10 | user10 | d7d61d43582dbae4395dc298bc8c6d80 |
+----+--------+----------------------------------+
10 rows in set (0.00 sec)
Empty set (0.00 sec)
mysql> insert into caidb.caitable(id,name,password) value ('17','user14',md5('usercai14'));
Query OK, 1 row affected (0.00 sec)
mysql> insert into caidb.caitable(id,name,password) value ('11','user14',md5('usercai14'));
Query OK, 1 row affected (0.00 sec)
mysql> insert into caidb.caitable(id,name,password) value ('12','user14',md5('usercai14'));
Query OK, 1 row affected (0.00 sec)
mysql> insert into caidb.caitable(id,name,password) value ('14','user14',md5('usercai14'));
Query OK, 1 row affected (0.00 sec)
mysql> create table `caitest` (`id` int(5) NOT NULL auto_increment, `name` varchar(32) NOT NULL, `password` char(32) NOT NULL, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.02 sec)
然后去slave上查看
[root@slave ~]# mysql -uroot -e "select * from caidb.caitable; use caidb; show tables"
+----+--------+----------------------------------+
| id | name | password |
+----+--------+----------------------------------+
| 1 | user1 | 848bbbf06aeba9495a47578569cee381 |
| 2 | user2 | 4b1700e322bf3619376ef6df33d4a68d |
| 3 | user3 | 6238fe4a6ae91fb4e4c364dab46f3570 |
| 4 | user4 | e695fa61724b69ef58b3ff38d6d26322 |
| 5 | user5 | f0bab2bf65014192d9bc6e139ba5d520 |
| 6 | user6 | be56605958ccaabfa9eeb6d5d33dd075 |
| 7 | user7 | fec2992b831f761e61c1f406f1c0e7ec |
| 8 | user8 | 4ab3f2d0a35ca849e86ddc9a7856500a |
| 9 | user9 | 77998b564bf7cd7db0df7b57538e93f8 |
| 10 | user10 | d7d61d43582dbae4395dc298bc8c6d80 |
| 12 | user14 | 335007a617140b0431d5c8dd51830e68 |
| 11 | user14 | 335007a617140b0431d5c8dd51830e68 |
| 17 | user14 | 335007a617140b0431d5c8dd51830e68 |
| 14 | user14 | 335007a617140b0431d5c8dd51830e68 |
+----+--------+----------------------------------+
+-----------------+
| Tables_in_caidb |
+-----------------+
| caitable |
| caitest |
+-----------------+
可见新插入的数据和新建的表caitest均已被复制过来了，至此mysql的主从复制配置完成！
##########################################
本文属于作者原创
作者：john
转载请注明出处
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
