Heim >Datenbank >MySQL-Tutorial >linux下数据库双向同步之mysql

linux下数据库双向同步之mysql

WBOY
WBOYOriginal
2016-06-07 14:52:481379Durchsuche

环境: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地址,虚拟机设置为桥接

image

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

image

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

 

image

image

image

image

前台无法进入:

[root@gjpmaster html]# vim wp-includes/template-loader.php

image

 

三、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了

image

 

四、数据库同步(单向)

从主站导入数据库

[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

image

同步好的数据

grant all on gjpmaster.* to gjpslave@localhost identified by "gjpmaster321";

由于上面账号设置成gjpslave 出错了,改为gjpmaster 和主站的一样,ok

image  

开始同步数据库:

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

从设备 

image

发表文章也是如此

 

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 对数据库进行管理

image

image

 

五、数据库的双向同步:

双向参考: 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=replace(option_value,'http://10.0.7.112', 'http://10.0.7.113') WHERE option_name='home' OR option_name='siteurl';

 

最后同步成功

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn