首页 > 代码库 > linux下数据库双向同步之mysql
linux下数据库双向同步之mysql
环境：lamp 下搭建一个博客 然后实现同步
这里：以wordpress为例，进行实施
具体操作如下：
一、环境配置
1.自动获取ip地址，虚拟机设置为桥接
2.[root@gjpslave ~]# yum install wget
#换成163的yum源：
cd /etc/yum.repos.d
mv CentOS-Base.repo CentOS-Base.repo.save
wget mirrors.163.com/.help/CentOS5-Base-163.repo
#wget mirrors.163.com/.help/CentOS6-Base-163.repo
sed -i ‘s/enabled=1/enabled=0/g‘ /etc/yum/pluginconf.d/fastestmirror.conf
cat /dev/null > /var/cache/yum/timedhosts.txt
yum clean all
yum makecache
3.yum -y install gcc* gcc-c++ rpm-build mysql* httpd* php* ppp openldap-devel lrzsz pam-devel wget net-snmp* ntp openssl* libgssapi* iptraf libtool-ltdl* libtool crontabs libpcap* flex byacc libpcap ncurses ncurses-devel automake autoconf libtool make traceroute wget scp openssh* setuptool mlocate pam.i686 openssl.i686 libgcrypt.i686 openssl098e.i686 glibc.i686 fping nload iftop iperf chkconfig unrar --skip-broken
yum -y update
4.系统安全
到dnspod.cn上为新的服务器指定域名
hostname xx 命名主机名
#### security ###
arp -a|grep $(/sbin/route -n|awk ‘$1=="0.0.0.0"{print $2}‘)|awk ‘{print $2 " " $4}‘|sed ‘s#(##g‘|sed ‘s#)##g‘ > /etc/ethers && arp -f
echo "SELINUX=disabled" > /etc/sysconfig/selinux
echo "SELINUXTYPE=targeted" >> /etc/sysconfig/selinux
setenforce 0
echo "HISTSIZE=5" >> /root/.bash_profile
chkconfig --level 2345 hplip off
chkconfig --level 2345 rpcidmapd off
chkconfig --level 2345 cups off
chkconfig --level 2345 portmap off
chkconfig --level 2345 yum-updatesd off
chkconfig --level 2345 iptables off
chkconfig --level 2345 sendmail off
killall cupsd portmap rpc.statd
service iptables stop
service yum-updatesd stop
service sendmail stop
echo "Port 2121" >> /etc/ssh/sshd_config
echo "ClientAliveInterval 9999" >> /etc/ssh/sshd_config
service sshd restart
二、博客搭建 wordpress
wget http://cn.wordpress.org/wordpress-3.9-zh_CN.tar.gz
tar zxvf http://cn.wordpress.org/wordpress-3.9-zh_CN.tar.gz
[root@gjpmaster tmp]# ls
hsperfdata_root wordpress wordpress-3.9-zh_CN.tar.gz yum.log yum_save_tx-2013-08
[root@gjpmaster tmp]# cp -a wordpress/* /var/www/html/
[root@gjpmaster ~]# useradd -d /var/www/html/ -s /sbin/nologin gjpmaster
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
[root@gjpmaster ~]# passwd gjpmaster
Changing password for user gjpmaster.
New password: 123456
BAD PASSWORD: it is too simplistic/systematic
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.
mysql> create database gjpmaster;
Query OK, 1 row affected (0.04 sec)
mysql> grant all on gjpmaster.* to gjpmaster@localhost identified by "gjpmaster321";
Query OK, 0 rows affected (0.00 sec)
[root@gjpmaster ~]# mysql -u gjpmaster -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, 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> quit
Bye
[root@gjpmaster html]# cp -a wp-config-sample.php wp-config.php
[root@gjpmaster html]# vi wp-config.php
[root@gjpmaster html]# chmod 777 -R wp-content/
[root@gjpmaster html]# vim /etc/httpd/conf/httpd.conf
<Directory /var/www/html>
Options -Indexes
RewriteEngine on
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?q=$1 [L,QSA]
</Directory>
[root@gjpmaster html]# service httpd restart
Stopping httpd: [ OK ]
Starting httpd: httpd: apr_sockaddr_info_get() failed for gjpmaster
httpd: Could not reliably determine the server‘s fully qualified domain name, using 127.0.0.1 for ServerName
[ OK ]
前台无法进入：
[root@gjpmaster html]# vim wp-includes/template-loader.php
三、ftp用来给博客上传下载东西
#安装
yum -y install vsftpd
#修改配置文件
vi /etc/vsftpd/vsftpd.conf
#system
local_umask=022
pasv_enable=YES
connect_from_port_20=YES
local_enable=YES
write_enable=YES
use_localtime=YES
listen=YES
listen_port=21
#security
guest_enable=NO
pam_service_name=vsftpd
tcp_wrappers=YES
userlist_enable=YES
userlist_deny=NO
userlist_file=/etc/vsftpd/user_list
chroot_local_user=YES
#chroot_list_enable=YES
#chroot_list_file=/etc/vsftpd.chroot_list
#log
xferlog_enable=YES
xferlog_std_format=YES
xferlog_file=/var/log/vsftpd.log
#anonymous
anonymous_enable=NO
#max_per_ip=13
#max_clients=50
#anon_world_readable_only=YES
#anon_other_write_enable=NO
#anon_mkdir_write_enable=NO
#anon_upload_enable=NO
no_anon_password=YES
#anon_max_rate=200000
#启动ftp
service vsftpd restart
#允许匿名登陆
修改配置文件 要有以下两条
no_anon_password=YES
anonymous_enable=YES
增加anonymous至/etc/vsftpd/user_list
#增加用户
两种情况 一种是有指定的目录分给指定的用户 如 将/var/ftp分给用户 wow 二是直接建立用户 目录就是/home/用户名
1.以将/var/ftp分配给wow为例
useradd -s /sbin/nologin -d /var/ftp wow // /sbin/nologin 是安全配置 意思是这个用户无法使用ssh登陆仅能使用ftp登陆 -d后面跟的是指定给此用户的目录
passwd wow //修改wow的用户名
echo wow >> /etc/vsftpd/user_list 这里一定要用>> 否则会清空里面的原来存放的用户名 只有这个列表user_list内的用户名可以登陆ftp上传文件 所以每添加一个用户名都要写入此文件
2.建立一个test用户 可以自己上传或下载文件
usreadd -s /sbin/nologin test
passwd test
echo test >> /etc/vsftpd/user_list
3.如果服务器上有多个ftp 在使用ftp协议下载时只能有一个采用匿名的方式 其它的都要带账号密码 比如说 ftp://123:123@sh02.lonlife.net/hehe.zip 还有另外一种方式就是给用户配置一个单独的域名用于下载 这时需要修改 /etc/httpd/conf/httpd.conf 增加如下 并重启http服务就好了
<VirtualHost *:80>
Options -Indexes
DocumentRoot "/old/home/bf2comcn" //ftp的目录
ServerName ftp.bf2.com.cn 绑定的域名
ErrorLog /var/log/httpd/bf2comcn_err.log
CustomLog /var/log/httpd/bf2comcn.log combined
</VirtualHost>
上面是例子，操作如下：
从设备：
[root@gjpslave html]# yum install vsftpd -y
[root@gjpslave vsftpd]# ls
ftpusers user_list vsftpd.conf vsftpd_conf_migrate.sh
[root@gjpslave vsftpd]# mv vsftpd.conf vsftpd.conf.bak
[root@gjpslave vsftpd]# vim vsftpd.conf
-bash: vim: command not found
[root@gjpslave vsftpd]# vi vsftpd.conf
[root@gjpslave vsftpd]# service vsftpd restart
Shutting down vsftpd: [ OK ]
Starting vsftpd for vsftpd: [ OK ]
[root@gjpslave vsftpd]# vi vsftpd.conf
[root@gjpslave vsftpd]# vi /etc/vsftpd/user_list
[root@gjpslave vsftpd]# useradd -s /sbin/nologin -d /var/www/html/ gjpslave
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.
[root@gjpslave vsftpd]# passwd gjpslave
Changing password for user gjpslave.
New password: 123456
[root@gjpslave vsftpd]# echo gjpslave >>/etc/vsftpd/user_list
[root@gjpslave vsftpd]# chmod 755 -R /var/www/html/
[root@gjpslave vsftpd]# chown gjpslave:gjpslave -R /var/www/html
后面将gjpslave还是改为gjpmaster了
四、数据库同步（单向）
从主站导入数据库
[root@gjpmaster www]# /usr/bin/mysqldump -u root -p gjpmaster>/tmp/20140528.sql;
从站从主站下载：
[root@gjpslave vsftpd]# scp -rP 22 10.0.7.112:/tmp/20140528.sql /tmp/20140528.sql
root@10.0.7.112‘s password:
20140528.sql 100% 408KB 407.6KB/s 00:00
mysql> create database gjpmaster;
Query OK, 1 row affected (0.03 sec)
mysql> use gjpmaster;
Database changed
mysql> source /tmp/20140528.sql;
Query OK, 18 rows affected (0.00 sec)
Records: 18 Duplicates: 0 Warnings: 0
同步好的数据
grant all on gjpmaster.* to gjpslave@localhost identified by "gjpmaster321";
由于上面账号设置成gjpslave 出错了，改为gjpmaster 和主站的一样，ok
开始同步数据库:
grant replication slave on *.* to "gjpmaster"@"10.0.7.113" identified by "gjpmaster321";
[root@gjpmaster tmp]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=gjpmaster
binlog-ignore-db=mysql
[root@gjpmaster tmp]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@gjpmaster tmp]# mysql -u root -p
mysql> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | gjpmaster | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
四、配置MySQL从服务器（192.168.21.129）的my.cnf文件
vi /etc/my.cnf #编辑配置文件，在[mysqld]部分添加下面内容
server-id=2 #设置服务器id，修改其值为2，表示为从数据库
log-bin=mysql-bin #启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
replicate-do-db=osyunweidb #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
replicate-ignore-db=mysql #不同步mysql系统数据库
read_only #设置数据库只读
:wq! #保存退出
service mysqld restart #重启MySQL
mysql -u root -p #进入MySQL控制台
show variables like ‘server_id‘; #查看server-id的值，必须为上面设置的2，否则请返回修改配置文件
mysql> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host=‘10.0.7.112‘,master_user=‘gjpmaster‘, master_password=‘gjpmaster321‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=106;
Query OK, 0 rows affected (0.05 sec)
change master to master_host=‘10.0.7.112‘,master_user=‘gjpmaster‘, master_password=‘gjpmaster321‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=316;
mysql> slave start;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.7.112
Master_User: gjpmaster
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gjpmaster
Replicate_Ignore_DB: mysql
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: 106
Relay_Log_Space: 407
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
测试：
主设备创建个表test
mysql> CREATE TABLE test ( id int not null primary key,name char(20) );
Query OK, 0 rows affected (0.07 sec)
CREATE TABLE lzw( id int not null primary key,name char(18) );
从设备
发表文章也是如此
wordpress迁移到其他服务器上 IP变动时候要修改以下信息.数据库里修改.
wp_options表:
siteurl 要改成新的IPurl
wp_posts表:
update wp_posts set post_content=replace(post_content,‘192.168.100.151‘,‘10.0.0.23‘)
update wp_posts set guid=replace(guid,‘192.168.100.151‘,‘10.0.0.23‘)
这里具体操作如下：
update wp_posts set post_content=replace(post_content,‘10.0.7.112‘,‘10.0.7.113‘)
update wp_posts set guid=replace(guid,‘10.0.7.112‘,‘10.0.7.113‘)
第一个域名是老域名 第二个是新的
UPDATE wp_options SET option_value=http://www.mamicode.com/replace(option_value,‘http://10.0.7.113‘, ‘http://10.0.7.112‘) WHERE option_name=‘home‘ OR option_name=‘siteurl‘;
在这里，可安装phpmyadmin 对数据库进行管理
五、数据库的双向同步：
双向参考： http://www.linuxidc.com/Linux/2012-10/71593.htm
主站：
useradd backup passwd backup 123
mysql> grant file on *.* to backup@‘10.0.7.113‘ identified by ‘123‘;
Query OK, 0 rows affected (0.00 sec)
mysql>flush privileges；
在slave上测试账户backup是否可以访问mster上的mysql：
#mysql -u backup -p -h 10.0.7.112（输入密码123，可以访问说明设置正确）
slave上也要建立账号 设密码
mysql> grant replication slave on *.* to "backup"@"10.0.7.113" identified by "123";
[root@gjpmaster ~]# cat /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=gjpmaster
binlog-ignore-db=mysql
max-binlog-size=104857600
master-host=10.0.7.113
master-user=backup
master-password=123
master-port=3306
replicate-do-db=gjpmaster
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@gjpmaster tmp]# service mysqld restart
mysql> slave stop;
mysql> change master to master_host=‘10.0.7.113‘,master_user=‘gjpmaster‘, master_password=‘gjpmaster321‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=478;
change master to master_host=‘10.0.0.139‘,master_user=‘gjpmaster‘, master_password=‘gjpmaster321‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=11395;
mysql> slave start;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.7.113
Master_User: gjpmaster
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 478
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从站：
mysql> grant replication slave on *.* to "gjpmaster"@"10.0.7.112" identified by "gjpmaster321";
[root@gjpslave html]# cat /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-do-db=gjpmaster
master-host=10.0.7.112
master-user=backup
master-password=123
master-port=3306
replicate-do-db=gjpmaster
#master-connect-retry=60
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
service mysqld restart
mysql> slave stop;
mysql> change master to master_host=‘10.0.7.112‘,master_user=‘gjpmaster‘, master_password=‘gjpmaster321‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=316;
change master to master_host=‘10.0.0.139‘,master_user=‘gjpmaster‘, master_password=‘gjpmaster321‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=106;
mysql> slave start;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.7.112
Master_User: gjpmaster
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 628
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 563
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gjpmaster
use gjpmaster;
update wp_posts set post_content=replace(post_content,‘10.0.7.112‘,‘10.0.7.113‘);
update wp_posts set guid=replace(guid,‘10.0.7.112‘,‘10.0.7.113‘);
第一个域名是老域名 第二个是新的
UPDATE wp_options SET option_value=http://www.mamicode.com/replace(option_value,‘http://10.0.7.112‘, ‘http://10.0.7.113‘) WHERE option_name=‘home‘ OR option_name=‘siteurl‘;
最后同步成功