Mysql主从,主备,新加从服务器

首先master上锁表,flush tables with read lock;解表示unlock tables;


基于bin-log实现

从有一个master-info文件(中继日志),从主的二进制文件到中继日志,中继日志生成mysql语句。

Show master status;

复制配置文件


[mysqld]

datadir=/data/mysql55/var

basedir=/usr/local/mysql55

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-bin=mysql-bin

server-id = 1


[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

replicate-do-db=all



Vi /etc/my.conf replicate后面改成all

再复制到从机器上,server-id不能一样

从不要bin-log

Replicate那行不要,因为同步哪个库是主来定的

History看历史命令的,|grep 加你想找的命令的关键词 !加条数执行

然后主上grant replication slave on *.* to 'root'@'192.168.245.130' identified by '1';

看下主上的show master status;

从只能从Position后同步

然后指定masterIP和请求同步的点,去从上粘贴命令change master to(配置文件里的)

change master to master_host='192.168.1.103',master_user='root',master_password='1',master_log_file='mysql-bin.000005',master_log_pos=624;


修改成服务器的IPuser改成rootmaster端的文件,和点

然后在从上启动slave start;

提示要fix在file里,SET GLOBAL server_id=149;

这个只是临时生效,要在配置文件里改的,

然后验证show slave status\G


MySQL mysqldump和xtrabackup备份

小于300G就mysqldump全备,大于300G就xtrabackup增量备。300G3-4个小时。可以说网站数据有50G,备份要半个小时

mysqldump -uroot -p zabbix(--all-databases是所有库) >zabbix0614.sql

xtrabackup 

 rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm;

yum -y install percona-xtrabackup;

rpm -qa |grep  xtrabackup|> /etc/null 2>&1


xtrabackup是用于热备份innodb, xtradb表中数据的工具,不能备份其他类型的表,也不能备份数据表结构;

innobackupex是将xtrabackup进行封装的perl脚本,提供了备份myisam表的能力。


基本语法innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/

报错

Can't load '/usr/local/lib64/perl5/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.18: 无法打开共享对象 at /usr/bin/innobackupex line 18cp

需要拷贝 cp /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib64/

tar 备份系统 

tar -cjpf backup.tar.bz2 --exclude=/proc --exclude=/lost+found --exclude=/mnt --exclude=/sys --exclude=/media /

只能备份myisam类型的表

保证你的拷贝完整性的最好方法是:关闭服务器,拷贝文件,然后重启服务器。
或者是,要锁定对应的表(对前端用户造成访问问题)。

 

解释物理直接拷贝文件,为什么不具备可移植性?


mysqldump 产生可移植到其他机器、甚至具有不同硬件结构的机器上的文本文件。直接拷贝文件不能够移植到其他机器上,除非要拷贝的表使用MyISAM 存储格式。ISAM 表只能在具有相同硬件结构的机器之间进行拷贝。例如,将文件从S PARC 的Solaris 机器拷贝到Intel 的Solaris 机器(或者相反)是行不通的。由MySQL3.23 引进的MyISAM 表存储格式可以解决这个问题,因为该格式与机器独立。因此,如果以下两个条件都满足的话,直接拷贝文件可以移植到具有不同硬件结构的机器上:即另一台机器上也必须运行MySQL3.23 以上的版本,并且文件必须表示成MyISAM 表,而不是ISAM 表。

用Bin-log日志恢复mysql数据

先查出哪个点出错了,关闭slave

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名 >backup.sql

去从上source backup.sql

 常用选项:

          --start-position=953                   起始pos点

          --stop-position=1437                 结束pos点

          --start-datetime="2013-11-29 13:18:54" 起始时间点

          --stop-datetime="2013-11-29 13:21:53"  结束时间点

          --database=zyyshop                     指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)


mysqlbinlog跟要看到binlog文件,提示mysqlbinlog: unknown variable 'default-character-set=utf8',mysqlbinlog --no-defaults --set-charset=utf8 mysql-bin.000001


IO_Running是yes,SQL_Runing是no,IO进程是通的,但是接收了数据不能执行,是因为从上多了命令,导致主从不同步

跳过错误>set global sql_slave_skip_counter =1;

有一个Read_master_Log_Pos点和Exec_Master_Log_Pos点,看看差多少

relay-log是中继日志

cat /usr/local/mysql55/var/master.info

可以看到binlog文件的名字,同步的pisition IP 用户名密码 端口

这个文件就是告诉从库下次从哪个点同步


读写分离

基于程序读写分离(效率高,实施难度大),这个需要开发改代码,运维只需要架好就可以直接分离了

三台服务器,主、从、中间代理

基于中间件,amoeba(貌似官方说不支持事务),mysql-proxy(官网很久没有更新了)


两个主从给proxy授权

>grant all on *.* to root@代理服务器identified by "1";

>flush privileges;

下载mysql-proxy,解压

useradd mysql-proxy

cd /usr/src

wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz    --no-check-certificate

tar zxf 

mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

在/etc/profile中加入如下语句

export PATH=$PATH:/usr/local/mysql-proxy/bin

source /etc/profile 使环境变量修改生效


启动,这个启动太长,可以把命令写道文件里,然后用sh 命令启动

/usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.0.46:3306" --proxy-read-only-backend-addresses="192.168.0.17:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

log-level提醒级别,可以写debug或warn其中backend是填写(主库)的IP,read-only是读(从库)的IP,proxy-lua-script写代理脚本的绝对路径,admin-username和admin-password是单独管理proxy的账号,admin-lua-script是lua脚本的绝对路径 


 /usr/local/mysql-proxy/libexec/mysql-proxy: cannot execute binary file应该是版本不对,换

启动之后会有两个端口,netstat -tnl 有两个端口,4040 proxy端口,4041 管理端口


然后网站得连代理服务器的4041端口,不能连原来的mysql服务器了,hosts里的mysql.com的IP改成代理服务器的,需要在配置文件里改发布目录下config里,config_global.php,pconnect这里改成4040,还有ucenter.php里的端口在主机后加:4040,网站就可以访问了

然后借助主从服务器上测试,#mysql -h代理服务器IP -uadmin -padmin -P 4041

>select * from backends;

#mysql -h192.168.0.56 -uroot -p -P 4040

读写状态都是未知,因为刷新的量不够


mysql主从高可用架构

mysql集群

MQ是放在MySQL主从中间,主从是异步复制,MQ把主上的日志存着,给从慢慢消化

MQ是一个消息通信中间件,可以用于通信的两台服务器

数据库20T,不可能是单机,肯定是集群

单台mysql,最大连接池2000

单台tomcat,php最大链接池500一下,跟网站代码有很大关系