Mysql主从复制[生产环境]

部署 Mysql 数据库主从复制
Deployment of mysql master-slave replication
( 操作系统：CentOS5.x ) [ 构建高效的、系统的、健壮的数据库 ] 文件状态： [ √ ] 草稿 [ √ ] 正式发布 [ √ ] 正在改进 [ √ ] 内部文档 文件标签： 当前版本： 作者： Mail & Qq： 完成日期： 数据库 Version 1.0 李凯翔 Net.Sharkshow@https://www.360docs.net/doc/11566605.html, & 17501444 2011 年 11 月 24 日
将数据库目录格式化 XFS 文件系统 XFS 的优点肯定高于 ext3 但是如果你的 mysql 多是小表.没必要追求用 XFS ## yum 安装相关组件
>
yum -y install kmod-xfs xfsdump xfsprogs xfsproges-devel
## 载入 xfs 文件系统模块
>
modprobe xfs
## 查看 xfs 模块是否载入
>
lsmod |grep xfs
## 先卸载分区，再格式分区成 xfs 格式
>
umount /dev/sda5 mkfs.xfs -f -i size=512 -l size=128m,lazy-count=1 -d agcount=16 /dev/sda5

## 修改自动挂载分区的设置
>
vi /etc/fstab /dev/sda5 /data xfs defaults,noatime,nodiratime,nobarrier,biosize=16,logbufs=8,logbsize=32768 0 0
## 再次挂载格式化后的分区
>
mount -t xfs /dev/sda5 /data
## 使用 time+dd 测试硬盘读写速度 ## 写速度：
>
time dd if=/dev/zero of=test.dbf bs=8k count=300000 300000+0 records in 300000+0 records out real user sys 0m36.669s 0m0.185s 0m9.340s
所以写速度为：8*30000/1024/36.669=63.916M/s ## 读速度：
>
time dd if=/dev/sda1 of=/dev/null bs=8k 448494+0 records in 448494+0 records out real user sys 0m51.070s 0m0.054s 0m10.028s
所以 sda1 上的读取速度为：8*448494/1024/51.070=68.61M/s
A. 配置主数据库
## 删除所有空用户、空密码、和自带空数据库，刷新一下立即生效 mysql> mysql> mysql> delete from https://www.360docs.net/doc/11566605.html,er where user='' or password=''; drop database test; flush privileges;
## 修改 root 密码，授权 web 查询，脚本查询，公司查询 mysql> mysql> mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'youpassword'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY ' youpassword '; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'从库内网 IP' IDENTIFIED BY ' youpassword';

mysql>
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'211.101.24.%' IDENTIFIED BY ' youpassword';
## 授权 replication 从库复制用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.1.%' IDENTIFIED BY 'youpassword';
## 配置前先停止 mysql 服务
>
/etc/init.d/mysqld stop
## 修改主库配置文件 ( 以下几处修改 )
>
vim /etc/https://www.360docs.net/doc/11566605.html,f ## 启用二进制日志
server-id = 7 log-bin = /data/mysql/3306/logs/binlog/mysql-bin log-error = /data/mysql/3306/logs/error.log log-slave-updates ## ## db 为只复制的数据库 . %=任意表 mysql 和 test 为不复制的数据库
replicate_wild_do_table= db.% repli cate_wild_ignore_table= mysql.% replicate_wild_ignore_table= test.% slave-skip-errors = all ## 修改默认引擎为 INNODB
default-storage-engine = INNODB ## 解释
使用 replicate_do_db 和 replicate_ignore_db 时有一个隐患，跨库更新时会出错 如设置 replicate_do_db=test mysql> use mysql; mysql> update test.table1 set ...... 第二句将不会被执行 如设置 replicate_ignore_db=mysql mysql> use mysql; mysql> update test.table1 set ...... 第二句会被忽略执行 原因是设置 replicate_do_db 或 replicate_ignore_db 后 MySQL 执行 sql 前检查的是当前默认数据库，所以跨库更新语句被忽略 可以使用 replicate_wild_do_table 和 replicate_wild_ignore_table 来代替

如 replicate_wild_do_table=test.% 和 replicate_wild_ignore_table=mysql.% 这样就可以避免出现上述问题了
## 启动 mysql 服务
>
/etc/init.d/mysqld start
## 查看默认引擎是否修改为 INNODB mysql> show engines;
## 加全局锁表以免在复制过程中数据发生变化 mysql> flush tables with read lock;
## 记录下 File 和 Position 状态 ; 从库需要使用 ( mysql-bin000126 和 877) mysql> show master status; +----------------------+-------------+----------------+-------------------+ | File | mysql-bin.000126 | Position | 877 | Binlog_Do_DB | Binlog_Ignore_DB | | | +----------------------+-------------+----------------+-------------------+ +----------------------+-------------+----------------+-------------------+ 1 row in set (0.00 sec) ## 主库解锁 mysql> UNLOCK TABLES;
## 手工同步，切换到数据库目录中，除了 mysql 库全部导到/tmp/下
> >
cd /data/mysql/3306/data/ for i in `ll | awk '$1~/^d/ && $NF !~ /mysql/{print $NF}'` ; do mysqldump -uroot -pPassWord --default-character-set=utf8 --opt --lock-all-tables ${i} > /tmp/${i}.sql ; done
## 将刚导出的数据库一次性复制到从库 -i 密钥文件 -P 指定端口 *.sql 通配符导出库名
>
scp -i /root/.ssh/sharkshow_dsa_1024 -P 59878 /tmp/*.sql root@192.168.1.2:/tmp

B.
配置从库
## 删除所有空用户、空密码、和自带空数据库，刷新一下立即生效 mysql> mysql> mysql> delete from https://www.360docs.net/doc/11566605.html,er where user='' or password=''; drop database test; flush privileges;
## 停止数据库服务
>
/etc/init.d/mysqld stop
## 修改从库配置文件 ( 以下几处修改 )
>
vim /etc/https://www.360docs.net/doc/11566605.html,f ## 启用二进制日志，read-only = 1 从库是否只读，0 表示可读写，1 表示只读 server-id = 8 log-bin = /data/mysql/3306/logs/binlog/mysql-bin log-error = /data/mysql/3306/logs/error.log log-slave-updates read-only = 1
## 代替密码端口等信息点文件. ( 文件名自定义 ) master-info-file=/data/mysql/3306/logs/relaylog/https://www.360docs.net/doc/11566605.html, relay-log=/data/mysql/3306/logs/relaylog/2-relay-bin relay-log-index=/data/mysql/3306/logs/relaylog/2-relay-log.index relay-log-info-file=/data/mysql/3306/logs/relaylog/https://www.360docs.net/doc/11566605.html,
## 防止跨库不更新的写法 ( db 为只复制的数据库 mysql 与 test 为不复制的数据库) replicate_wild_do_table= db.% replicate_wild_ignore_table= mysql.% replicate_wild_ignore_table= test.% slave-skip-errors = all # 修改默认存储引擎 default-storage-engine = MyISAM
## 启动 mysql 服务
>
/etc/init.d/mysqld start

## 查看默认引擎是否修改为 MyISAM mysql> show engines;
##
创建数据库，并且挨个导入库 create database 'db_name'; source /tmp/db.sql;
mysql> mysql> ##
停止从状态 stop slave;
mysql>
## 配置连接到主库的信息点.授权用户.密码.端口.会生成到从库 https://www.360docs.net/doc/11566605.html,f 指定的信息点文件中 mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.99',MASTER_USER='replica',MASTER_PASSWORD='youpassword',M ASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000126',MASTER_LOG_POS=877;
## 再次开启从状态 mysql> start slave;
## 查看从的复制状态是否正常 mysql> show slave status\G;
A1 主库的表引擎修改
## 切换到数据库目录并将所有表转换成 INNODB 格式
> >
cd /data/mysql/3306/data/ for i in `ll | awk '$1~/^d/ && $NF !~ /mysql/{print $NF}'`;do mysql -uroot -pyoupassword -e "show tables in ${i};" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > /tmp/innodb_${i}.sql ;done
## 禁止产生 binlog ( 防止操作被同步 ) mysql> SET SESSION SQL_LOG_BIN = 0;

## 依次导入转换后属于 INNODB 的 sql 语句 mysql> mysql> use db; source /tmp/innodb_Name.sql;
## 导入完成后继续开启 binlog 实现同步 mysql> SET SESSION SQL_LOG_BIN = 1;
## 查看除了 mysql 的数据库其它数据库的表是否为 INNODB 格式
> >
cd /data/mysql/3306/data/ for i in `ll | awk '$1~/^d/ && $NF !~ /mysql/{print $NF}'`;do mysql -uroot -pyoupassword -e "show table status" -D ${i}|sed '1d' | awk '{print $1" "$2}';done
B1
> >
从库的表引擎修改
cd /data/mysql/3306/data/ for i in `ll | awk '$1~/^d/ && $NF !~ /mysql/{print $NF}'`; do mysql -uroot -pyoupassword -e "show tables in ${i};" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=MYISAM;" > /root/sharkshow/hehe/myisam_${i}.sql ; done
## 切换到数据库目录并将所有表转换成 MyISAM 格式
## 禁止产生 binlog ( 防止操作被同步 ) mysql> SET SESSION SQL_LOG_BIN = 0;
## 依次导入转换后属于 MyISAM 的 sql 语句 mysql> mysql> use db; source /tmp/myisam_Name.sql
## 导入完成后继续开启 binlog 实现同步 mysql> SET SESSION SQL_LOG_BIN = 1;
## 查看除了 mysql 的数据库其它数据库的表是否为 MyISAM 格式
> >
cd /data/mysql/3306/data/ for i in `ll | awk '$1~/^d/ && $NF !~ /mysql/{print $NF}'`;do mysql -uroot -pyoupassword -e "show table status" -D ${i}|sed '1d' | awk '{print $1" "$2}';done