MySQL Master-Slave Replication
Offical reference
http://dev.mysql.com/doc/refman/5.0/en/replication.html
简化步骤：
1. download
mySQL for centOS5 (RHEL5)建议intel cc编译的这个版本，建议下载5.1版本,6.0的falcon性能并不比innoDB好
Linux (non RPM, Intel C/C++ compiled, glibc-2.3) downloads
配置默认网关：/etc/sysconfig/network
配置dhcp或者静态IP在/etc/network-scripts/ifcfg-eth0, 1, 2...
配置dns hostname在/etc/resolv.conf
2. 如果已经有了mysql那么先 rpm -ev mysql
然后关闭iptables防火墙
3. 创建用户
groupadd mysql
useradd -g mysql mysql
passwd mysql
4. 解开压缩包然后复制到三份，master-slave-slave
cp -r mysql* /opt/mysqlM
cp -r mysql* /opt/mysqlS0
cp -r mysql* /opt/mysqlS1
到/opt下执行
chown -R mysql *
chgrp -R mysql *
到mysqlM,和另外两个数据库下执行
scripts/mysql_install_db --user=mysql
chown -R mysql data
chgrp -R mysql data
5. 启动主数据库
./bin/mysqld_safe --user=mysql &
然后测试数据库状态：
[root@arsenal14 mysqlM]# bin/mysqladmin version
bin/mysqladmin Ver 8.42 Distrib 5.1.26-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.26-rc
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 min 13 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.13
关闭数据库 ./mysqladmin shutdown
创建配置文件，把support-files里的my-medium.conf复制到mysqlM/my.cnf
更改一些参数,比如
port=4306
socket=/tmp/mysqlM.sock
collation_server=utf8_unicode_ci
character_set_server=utf8
打开innoDB的配置等
然后用这个命令行启动mysql:
用ping看看数据库状态
如果你需要用root通过远程客户端登录，你需要enable mysql 的root remote login
6. 配置两个slave数据库
[root@arsenal14 mysqlM]# cp my.cnf ../mysqlS0
[root@arsenal14 mysqlM]# cp my.cnf ../mysqlS1
然后只要改端口和sock文件不要重复，innoDB数据文件路径不重复，pid file 不重复就可以了
建议改成
检查是否可以启动，查看err日志
[root@arsenal14 mysqlS0]# bin/mysqld_safe --defaults-file=/opt/mysqlS0/my.cnf --user=mysql &
[root@arsenal14 mysqlS1]# bin/mysqld_safe --defaults-file=/opt/mysqlS1/my.cnf --user=mysql &
7. 准备知识
现在才开始真正配置master-slave，有些基础知识要知道
1. master-slave之间使用master的数据库日志来让slave知道master发生了什么
2. 除了master-slave模式，mySQL现在cluster也是免费的了
3. mySQL proxy 是一个代理组件，上面可以运行mysql load balancer
4. mysql load balancer的好处：
4.1 自动排除掉同步失败或者落后的slave
4.2 修复后同步正常的slave可以自动加入
4.3 把读取分布转发到集群内任何一台服务器
4.4 写不可以分发
4.5 保持各个数据库链接数平均
8. 配置主数据库
先关闭数据库 ./mysqladmin shutdown
在master上创建同步用的用户,我用了root偷懒
接下来要master打开日志，并标识一个ID
[mysqld]
log-bin=mysql-bin
server-id=1
sync_binlog=1
binlog-do-db=test
9. 配置一个slave数据库
指定一个id
[mysqld]
server-id=2
master-host =arsenal14.asiapacific.hpqcorp.net
master-user =mysqlrep
master-password =password
master-port =4036
replicate-do-db=test
如果master已经有了数据，那么你要停止master的数据操作，先dump到slave保持一致才能开始replicationOffical reference
http://dev.mysql.com/doc/refman/5.0/en/replication.html
简化步骤：
1. download
mySQL for centOS5 (RHEL5)建议intel cc编译的这个版本，建议下载5.1版本,6.0的falcon性能并不比innoDB好
Linux (non RPM, Intel C/C++ compiled, glibc-2.3) downloads
配置默认网关：/etc/sysconfig/network
配置dhcp或者静态IP在/etc/network-scripts/ifcfg-eth0, 1, 2...
配置dns hostname在/etc/resolv.conf
2. 如果已经有了mysql那么先 rpm -ev mysql
然后关闭iptables防火墙
[root@arsenal14 mysqlM]# chkconfig --list iptables
iptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@arsenal14 mysqlM]# service iptables save
Saving firewall rules to /etc/sysconfig/iptables: [ OK ]
[root@arsenal14 mysqlM]# service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
[root@arsenal14 mysqlM]# chkconfig iptables off
[root@arsenal14 mysqlM]# chkconfig --list iptables
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
iptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@arsenal14 mysqlM]# service iptables save
Saving firewall rules to /etc/sysconfig/iptables: [ OK ]
[root@arsenal14 mysqlM]# service iptables stop
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
[root@arsenal14 mysqlM]# chkconfig iptables off
[root@arsenal14 mysqlM]# chkconfig --list iptables
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
3. 创建用户
groupadd mysql
useradd -g mysql mysql
passwd mysql
4. 解开压缩包然后复制到三份，master-slave-slave
cp -r mysql* /opt/mysqlM
cp -r mysql* /opt/mysqlS0
cp -r mysql* /opt/mysqlS1
到/opt下执行
chown -R mysql *
chgrp -R mysql *
到mysqlM,和另外两个数据库下执行
scripts/mysql_install_db --user=mysql
chown -R mysql data
chgrp -R mysql data
5. 启动主数据库
./bin/mysqld_safe --user=mysql &
然后测试数据库状态：
[root@arsenal14 mysqlM]# bin/mysqladmin version
bin/mysqladmin Ver 8.42 Distrib 5.1.26-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.1.26-rc
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 min 13 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.13
关闭数据库 ./mysqladmin shutdown
创建配置文件，把support-files里的my-medium.conf复制到mysqlM/my.cnf
更改一些参数,比如
port=4306
socket=/tmp/mysqlM.sock
collation_server=utf8_unicode_ci
character_set_server=utf8
打开innoDB的配置等
然后用这个命令行启动mysql:
mysqld_safe --defaults-file=/opt/mysqlM/my.cnf --user=mysql
用ping看看数据库状态
[root@arsenal14 mysqlM]# bin/mysqladmin --defaults-file=/opt/mysqlM/my.cnf ping
mysqld is alive
mysqld is alive
如果你需要用root通过远程客户端登录，你需要enable mysql 的root remote login
[root@arsenal14 mysqlM]# bin/mysql --defaults-file=/opt/mysqlM/my.cnf
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.26-rc-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
然后你就可以用toad for mysql了Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.26-rc-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
6. 配置两个slave数据库
[root@arsenal14 mysqlM]# cp my.cnf ../mysqlS0
[root@arsenal14 mysqlM]# cp my.cnf ../mysqlS1
然后只要改端口和sock文件不要重复，innoDB数据文件路径不重复，pid file 不重复就可以了
建议改成
port=5306
socket = /tmp/mysqlS.sock
innodb_data_home_dir = /opt/mysqlS0/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysqlS0/data/
pid-file=/opt/mysqlS0/pid
port = 6306
socket = /tmp/mysqlS1.sock
innodb_data_home_dir = /opt/mysqlS1/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysqlS1/data/
pid-file=/opt/mysqlS1/pid
socket = /tmp/mysqlS.sock
innodb_data_home_dir = /opt/mysqlS0/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysqlS0/data/
pid-file=/opt/mysqlS0/pid
port = 6306
socket = /tmp/mysqlS1.sock
innodb_data_home_dir = /opt/mysqlS1/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysqlS1/data/
pid-file=/opt/mysqlS1/pid
检查是否可以启动，查看err日志
[root@arsenal14 mysqlS0]# bin/mysqld_safe --defaults-file=/opt/mysqlS0/my.cnf --user=mysql &
[root@arsenal14 mysqlS1]# bin/mysqld_safe --defaults-file=/opt/mysqlS1/my.cnf --user=mysql &
7. 准备知识
现在才开始真正配置master-slave，有些基础知识要知道
1. master-slave之间使用master的数据库日志来让slave知道master发生了什么
2. 除了master-slave模式，mySQL现在cluster也是免费的了
3. mySQL proxy 是一个代理组件，上面可以运行mysql load balancer
4. mysql load balancer的好处：
4.1 自动排除掉同步失败或者落后的slave
4.2 修复后同步正常的slave可以自动加入
4.3 把读取分布转发到集群内任何一台服务器
4.4 写不可以分发
4.5 保持各个数据库链接数平均
8. 配置主数据库
先关闭数据库 ./mysqladmin shutdown
在master上创建同步用的用户,我用了root偷懒
接下来要master打开日志，并标识一个ID
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1sync_binlog=1
binlog-do-db=test
9. 配置一个slave数据库
指定一个id
[mysqld]
server-id=2
master-host =arsenal14.asiapacific.hpqcorp.net
master-user =mysqlrep
master-password =password
master-port =4036
replicate-do-db=test
10.配置master-slave
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
这里bin 日志是000006，偏移量是106，记下来这个数据
在命令行下导出master的数据
./bin/mysqldump --defaults-file=/opt/mysqlM/my.cnf --all-databases --lock-all-tables >dbdump.db
然后倒入到slave中。
./bin/mysql -h master <>最后在slave配置目标master
这里应该是
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='arsenal14.asiapacific.hpqcorp.net', master_port=4306, master_user='mysql', master_password='password', master_log_file='mysql-bin.000010', master_log_pos=106;
Query OK, 0 rows affected (0.02 sec)
好了，重新启动slave，然后对master新建一个表看看,不行的话要改配置需要先删除data/maseter.info，然后再执行上面的sql
然后你可以看到第三条数据是自动复制过来的，貌似create table没有正常复制过来，自己创建了table之后数据就复制过来了，钱两行因为没有表所以丢失了
mysql复制有两种，statement和record的，DDL比如创建表同步这个问题需要看一下
我又重新做了测试，这次DDL也可以同步了，数据也是正确的。刚才的问题一直没有再次重现，比较奇怪。
博文
没有评论:
发表评论