Mysql同步复制
时间：2008-09-12 来源：zlj2208
<link style="font-family: 宋体;" rel="File-List" href="file:///C:%5CDOCUME%7E1%5CZHOULI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><style> </style>
==========================================================
Mysql同步复制
==========================================================
一、建立数据
master:
1、创建数据
mysql> create database mytestdb;
mysql> use mytestdb;
Database changed
2、创建1个表
mysql> create table t1(id int ,name varchar(10));
Query OK, 0 rows affected (0.04 sec)
3、将创建好的数据库导出，整到从服务器上。
[root@fedora data]# mysqldump -uroot -p123456 mytestdb>/home/zhoulj/mytestdb.sql
scp /home/zhoulj/mytestdb.sql [email protected]:~
4、授权远程可以访问改服务器
mysql> grant all on *.* to 'root'@'%' identified by '123456'
slave:
1、创建数据库，名字和主数据的名字相同
mysql> create database mytestdb;
mysql> use mytestdb;
2、将在主服务器上建好的备份文件导入到改数据中
[root@fedora data]# mysql -u root -p123456 mytestdb </home/zhoulj/mytestdb.sql
二、修改配置
master:
在/etc/my.cnf文件中加入
启用bin-log,指定备份的数据
log-bin
binlog-do-db = mytestdb
slave：
在/etc/my.cnf文件中加入
server-id = 2
master-host = 10.4.5.221
master-user = root
master-password = 123456
master-port = 3306
replicate-do-db = mytestdb
log-bin
三、启动服务
1、启动主mysql
2、启动从mysql
3、在从服务器启动备份
[root@fedora data]# mysql -uroot -p123456 -e 'start slave'
4、查看状态
[root@fedora data]# mysql -uroot -p123456 -e 'show slave status\G'
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.5.221
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: fedora-bin.000005
Read_Master_Log_Pos: 148
Relay_Log_File: fedora-relay-bin.000006
Relay_Log_Pos: 236
Relay_Master_Log_File: fedora-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mytestdb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 148
Relay_Log_Space: 236
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
[root@fedora data]#
验证：
master：
mysql> insert into t1 values(1,'aaa');
Query OK, 1 row affected (0.08 sec)
mysql> insert into t1 values(2,'bbb');
Query OK, 1 row affected (0.08 sec)
slave：
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
正确，在主服务器插入记录，从服务器也跟着插入记录，同步复制功能正常。
四、主从切换
要想完成主从切换，必须删除从服务器上的几个文件，要不然不行，因为该文件中记录着一些信息。
master.info
relay.info
[root@fedora data]# cat master.info
14
fedora-bin.000007
148
10.4.5.222
root
123456
3306
60
0
[root@fedora data]# cat relay-log.info
./fedora-relay-bin.000018
4
fedora-bin.000007
148
8
最好也删除bin-log文件
将主配置文件和从配置文件都写好，名字分别为
my-master.cnf
my-slave.cnf
在切换时候直接cp成my.cnf即可。
下面是切换脚本：
切换到主服务器的脚本
[root@fedora data]# cat /etc/init.d/changeToMaster
#!/bin/bash
#
#
pid=`ps aux|grep mysql|awk '{print $2}'`
echo "......"
echo "change mysql to master."
echo ""
echo "stop mysql server"
if [ -n pid ]
then
/etc/init.d/mysql stop
echo ""
echo "mysql server stoped."
echo ""
fi
cp /etc/my-master.cnf /etc/my.cnf
if [ -f /usr/local/mysql/data/master.info ]
then
rm -rf /usr/local/mysql/data/master.info
echo "removed master.info "
rm -rf /usr/local/mysql/data/relay-log.info
fi
echo "starting mysql server."
/etc/init.d/mysql start
echo "mysql server started."
echo ""
echo "mysql server work at master."
echo "......"
切换到从服务器的脚本
[root@fedora data]# cat /etc/init.d/changeToSlave
#!/bin/bash
#
#
binpath="/usr/local/mysql/bin/"
pid=`ps aux|grep mysql|awk '{print $2}'`
echo "......."
echo "change mysql server to Slave."
echo ""
echo "stop mysql server"
if [ -n pid ]
then
/etc/init.d/mysql stop
echo "mysql server stoped."
fi
cp /etc/my-slave.cnf /etc/my.cnf
if [ -f /usr/local/mysql/data/master.info ]
then
rm -rf /usr/local/mysql/data/master.info
echo "removed master.info "
rm -rf /usr/local/mysql/data/relay-log.info
fi
echo "starting mysql server."
/etc/init.d/mysql start
echo "mysql server started."
echo ""
echo "mysql server work at Slave."
echo ""
sleep 5
/usr/local/mysql/bin/mysql -uroot -p123456 -e 'start slave'
/usr/local/mysql/bin/mysql -uroot -p123456 -e 'show slave status\G'
相关阅读 更多 +
