linux下数据库双向同步之mysql-mysql教程-PHP中文网

首页

数据库

mysql教程

linux下数据库双向同步之mysql

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:52 PM

lamplinuxmysql同步数据库环境

环境：lamp 下搭建一个博客然后实现同步这里：以wordpress为例，进行实施具体操作如下：一、环境配置 1.自动获取ip地址，虚拟机设置为桥接 650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px;" title="i

环境：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

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

Options -Indexes

RewriteEngine on

RewriteBase /

RewriteCond %{REQUEST_FILENAME} !-f

RewriteCond %{REQUEST_FILENAME} !-d

RewriteRule ^(.*)$ index.php?q=$1 [L,QSA]

[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服务就好了

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

上面是例子，操作如下：

从设备：

[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;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

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

*************************** 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=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