20170406-mysql-ab复制-双向复制-反向代理-读写分离

ab复制： master : 192.168.30.133  server:192.168.30.134

master: yum install mysql-server -y  

   vim /etc/my.cnf  

      [mysqld] 下面 加：

    character-set-server=utf8

    log-bin=mysql-bin

    server-id=1

   innodb_data_file_path=ibdata1:10M:autoextend
   innodb_log_file_size=5242880
   innodb_log_files_in_group=2

  保存退出  

  service  mysqld restart  

yum install  meb  -y

mysqlbackup -uroot -p123456 --defaults-file=/etc/my.cnf --socket=/var/lib/mysql/mysql.sock --backup-dir=/tmp/backups backup

ls /tmp/backups/
backup-my.cnf datadir meta server-all.cnf server-my.cnf

vim /tmp/backup123/backup-my.cnf
[mysqld]
6 innodb_data_file_path=ibdata1:10M:autoextend
7 innodb_log_file_size=5242880
8 innodb_log_files_in_group=2
9 innodb_page_size=16384
10 innodb_checksum_algorithm=none

mysql -uroot -p123456 

grant replication slave on *.* to rep@'192.168.30.134' identified by 'rep123';

 

slave: 192.168.30.134

yum  install mysql-server   

yum install meb 

vim /etc/my.cnf

innodb_data_file_path=ibdata1:10M:autoextend
innodb_log_file_size=5242880

innodb_log_files_in_group=2

character-set-server=utf8  

relay-log=relay-bin

server-id=2

保存退出  

service mysqld restart 

service mysqld stop  

scp -r root@192.168.30.133:/tmp/backups /tmp/ 

mysqlbackup --backup-dir=/tmp/backups/ apply-log

mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/backups  --datadir=/var/lib/mysql/ copy-back 

chown mysql.mysql /var/lib/mysql -R 

service mysqld restart

查询2进制日志文件：

  vim /tmp/backup123/meta/backup_variables.txt 

  binlog_position=mysqld-bin.000001:106

mysql -uroot -p123456

change master to master_host="192.168.30.133", master_port=3306, master_user="rep", master_password="rep123", master_log_file="mysql-bin.000001", master_log_pos=106;

show slave status \G

start slave;

现在192.168.30.134就可以从192.168.30.133的数据库同步了

二： 在ab复制的基础上，互相复制：

slave:192.168.30.134 

vim /etc/my.cnf 

在 [mysqld]下 补加

log-bin=mysql-bin

service mysqld restart 

mysql -uroot -p123456

grant replication slave on *.* to rep@'192.168.30.133' identified by 'rep1234';

show master status;

mysqld-bin.000001     106

 

master:192.168.30.133

vim /etc/my.cnf 

在 [mysqld] 下 补加

relay-log=relay-bin

service mysqld restart  

mysql -uroot -p123456

flush tables with read lock;

change master to master_host="192.168.30.134", master_port=3306, master_user="rep", master_password="rep1234", master_log_file="mysqld-bin.000001", master_log_pos=106; (这里是上面在192.168.30.134查询出来的信息)

start slave;

unlock tables;

现在192.168.30.133和192.168.30.134就可以互相同步数据库信息了。

三：反向代理

客户端：192.168.30.35

yum install mysql-router

vim /etc/mysqlrouter/mysqlrouter.ini

[routing:xb]

bind_address=0.0.0.0

bind_port=3333

mode=read-write(主次) 或者read-only(轮循)

destinations = 192.168.30.133:3306, 192.168.30.134:3306

/etc/init.d/mysqlrouter restart

在192.168.30.134或者192.168.30.133数据库中创建用户授权

grant all on *.* to tom@'%' identified by '123456';

在192.168.30.35： 

测试： mysql -utom -p123456 -h 192.168.30.35 -P 3333 

 轮循模式下 192.168.30.134和192.168.30.133需要能互相同步

四： 读写分离： 

软件： kingshard  用go语言写的

编译好kingshard 

cd kingshard/ 

cp etc/unshard.yaml  etc/class.yaml

vim etc/class.yaml

#server listen addr
addr : 0.0.0.0:3304

# server user and password
user : class

password: uplooking 

 # all mysql in a node must have the same user and password
 user: tom

password: 123456

#master represents a real mysql master server 

master : 192.168.30.133:3306

slave : 192.168.30.134:3306

保存退出

./bin/kingshard -config etc/calss.yaml

mysql -uclass -puplooking -P 3304 -h 192.168.30.35   这里的用户和密码是配置文件中写的那个

 

 

 

 

 

 

 

转载于:https://my.oschina.net/u/3217381/blog/874425