Mysql的主从复制实践
1.两台MySQL服务器
1.主服务器：192.168.1.123 账号：root 密码：root
2.从服务器：192.168.1.246 账号：root 密码：root
2.配置MySQL主服务器(192.168.1.123)
1.cmd进入dos窗口
2.登录mysql，进入MySQL控制台： mysql -uroot -proot -h127.0.0.1 -P3306
3.创建需要进行主从同步的数据库： create database test;
4.在主MySQL服务器的配置文件my.cnf(Linux环境)或my.ini(Windows环境)中，在[mysqld]节点下，添加如下内容：
[mysqld]
#配置主从复制Master开始
server-id=1 #设置服务器id，为1表示主服务器，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
log_bin=mysql-bin #二进制文件必须开启，启动MySQL二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
binlog-do-db=test #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
binlog-ignore-db=mysql #不同步mysql系统数据库
innodb_flush_log_at_trx_commit=2#日志flush到磁盘的，2表示写入到缓存，提高性能，操作系统崩溃会丢失部分数据
binlog_checksum=none#特别注意：由于 master 用的 mysql5.6 , binlog_checksum 默认设置的是 crc32。 如果slave用的 5.5 或者更早的版本，请将master的 binglog_checksum设置为 none
#配置主从复制结束
5.重启Mysql服务 Service mysql restart(Linux环境)，Windows环境，找到服务管理列表，重启MySQL服务
6. 创建同步用户并授权，在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICAITON SLAVE权限。
--- 该连接账号 用户名：replication(自定义) 密码：123456(自定义)
--- 这个账号，在Salve从服务器连接主服务器进行数据同步的时候需要
在主服务器登陆的mysql上执行：grant replication slave on *.* to 'replication'@'192.168.1.246' identified by '123456';
注： replication@192.168.1.246这里是客户端的ip 可以使用 % 代替，表示允许任意的客户端，例如：
192.168.1.% 。表示该段地址的主机都可作为客户端。
注意：也可在mysql中新建一个用于主从数据库同步的用户：replication，密码是：123456，然后在进行授权，操作如下：
方案二：创建同步用户并授权
#创建用户replication，密码是123456
insert into mysql.user(Host,User,Password) values('localhost','replication',password('123456'));
#建立MySQL主从数据库同步用户replication密码123456
flush privileges; #刷新系统授权表
#授权用户replication只能从192.168.1.246这个IP访问主服务器192.168.1.123上面的数据库，并且只具有数据库备份的权限
grant replication slave on *.* to 'replication'@'192.168.1.246' identified by '123456' with grant option;
7.查看主服务器状态：
show master status
特别注意：这里的的File：mysql-bin.000008和Position：1079，后面会在配置从服务器的时候使用到
3.配置MySQL从服务器(192.168.1.246)
我这里是Linux下的MySQL作为从服务器
1.配置MySQL从服务器的my.cnf文件
vi /etc/my.cnf #编辑配置文件，在[mysqld]部分添加下面内容
server-id=2 #配置文件中已经有一行server-id=1，修改其值为2，表示为从数据库
log-bin=mysql-bin #启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了。
replicate-do-db=osyunweidb #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
replicate-ignore-db=mysql #不同步mysql系统数据库
:wq! #保存退出
2.重启MySQL：
service mysql restart;
3. 先停止slave进程，执行同步语句，然后再开启slave同步进程
注意：这里执行同步语序用到了，主服务器中配置的同步用户：replication，123456
和 master的信息，File： mysql-bin.000008，Position：1079
代码如下：
mysql> slave stop; #停止slave同步进程
mysql> change master to master_host='192.168.1.123',
master_user='replication',master_password='123456',
master_log_file='mysql-bin.000008', master_log_pos=1079;
mysql>slave start; #开启slave同步进程
4.查看从服务器状态：
SHOW SLAVE STATUS\G #查看slave同步信息
注意查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上这两个参数的值为Yes，即说明配置成功！否则都是错误的状态(如：其中一个NO均属错误)
4.测试MySQL主从服务器复制双机热备是否成功
1.进入MySQL主服务器：-- 注意：这里主服务器我用的是本机，所以-h可以使用127.0.0.1或192.168.1.123
mysql -uroot -proot -h127.0.0.1 -P3306
use test;
2.创建表并插入数据
mysql> create table staff(
-> id int not null auto_increment primary key,
-> name varchar(50),
-> age int
-> );
mysql> insert into staff(id,name,age) values(1,'staff1',11);
3.进入MySQL从服务器，查看数据库中表和记录是否已经同步
mysql -uroot -proot -h192.168.1.246 -P3306
use test;
show tables; #查看staff表结构，会看到有一个新建的staff，表示数据库同步成功
5.配置过程中遇到的问题：
1.查看Mysql从服务器状态时出现如下错误：how slave status;
Last_IO_Error: Got fatal error 1236 from master when reading data from >> binary log: 'Slave can not handle replication events with the checksum that >> master is configured to log; the first event 'mysql-bin.000008'这是由于 master 用的 mysql5.6 , binlog_checksum 默认设置的是 crc32。 如果slave用的 5.5 或者更早的版本，请将master的 binglog_checksum设置为 none。
binlog_checksum=none
即：在Master的my.cnf或my.ini中，mysqld节点下添加代码：
binlog_checksum=none
MySQL的读写分离-- 使用Amoeba中间件实现
参考Amoeba官方文档：http://docs.hexnova.com/amoeba/rw-splitting.html
1.dbServers.xml的配置 指定两个服务器：Master 127.0.0.1(192.168.1.123)(可读写)， Slave 192.168.1.246(只读)
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">
<!--
Each dbServer needs to be configured into a Pool,
If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
such as 'multiPool' dbServer
-->
<dbServer name="abstractServer" abstractive="true">
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">${defaultManager}</property>
<property name="sendBufferSize">64</property>
<property name="receiveBufferSize">128</property>
<!-- mysql port -->
<property name="port">3306</property>
<!-- mysql schema -->
<property name="schema">test</property>
<!-- mysql user -->
<property name="user">root</property>
<!-- mysql password -->
<property name="password">root</property>
<!-- mysql password
<property name="password">password</property>
-->
</factoryConfig>
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">500</property>
<property name="maxIdle">500</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testOnReturn">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>
<dbServer name="server1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">127.0.0.1</property>
</factoryConfig>
</dbServer>
<dbServer name="server2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.246</property>
</factoryConfig>
</dbServer>
<dbServer name="multiPool" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">server1,server2</property>
</poolConfig>
</dbServer>
</amoeba:dbServers>2.配置 amoeba.xml
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
...
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">server1</property>
<!-- 配置读写分离 -->
<property name="writePool">server1</property>
<property name="readPool">server2</property>
<!--
<property name="writePool">server1</property>
<property name="readPool">server1</property>
-->
<property name="needParse">true</property>
</queryRouter>3.通过使用isReadStatement在rule.xml配置中指定读库和写库
我的配置：
<!-- 自定义配置的分片规则，设置需要分片的表、数据库名、分片的服务器、分片规则：对ID mod2，分别分片到server1、server2-->
<tableRule name="staff" schema="test" defaultPools="server1,server2">
<rule name="rule1" ruleResult="POOLNAME">
<parameters>ID</parameters>
<!-- 设置切片规则：水平切分-->
<!--
<expression><![CDATA[
var division = ID%2;
case division when 0 then 'server1';
when 1 then 'server2';
end case;
]]></expression>
-->
<!--配置读写分离规则-->
<expression><![CDATA[
case isReadStatement when false then 'server1';
when true then 'server2';
end case;
]]></expression>
<defaultPools>server1</defaultPools>
</rule>
</tableRule>
官方举例：
<?xml version="1.0" encoding="gbk"?>
<!DOCTYPE amoeba:rule SYSTEM "rule.dtd">
<amoeba:rule xmlns:amoeba="http://amoeba.meidusa.com/">
...
<tableRule name="MESSAGE" schema="test" defaultPools="blogdb-1-write,blogdb-2-write">
<rule name="rule1" ruleResult="POOLNAME">
<parameters>ID</parameters>
<expression><
when 1 then (isReadStatement?'blogdb-2-read':'blogdb-2-write');
]]></expression>
<defaultPools>blogdb-1</defaultPools>
</rule>
</tableRule>
...
</amoeba:rule>
MySQL主从架构配置案例 - - 参考
一般规范一点的公司，为了服务器的安全稳定，都要进行主从配置，而且开发人员也希望主从配置，因为只有一台机器工作的话，一旦宕机，工程师就得日日夜夜加班搞，但是有主从机的架构就不一样了。
注：如果刚开始不是主从架构，主库单独跑了一段时间后要改为主从，那需要保证数据库data一致。
1. 主库编辑my.cnf
mysql代码
[mysqld]
log-bin=mysql-bin
server-id=1 #配一个唯一的ID编号，(2-2的32次幂)-1。
#设置要进行或不要进行主从复制的数据库名，同时也要在 Slave 上设定。
#binlog-do-db=数据库名1
#binlog-do-db=数据库名2
#binlog-ignore-db=数据库名1
#binlog-ignore-db=数据库名2
binlog-do-db=testdb
重启mysql 服务后，连接数据库。
java代码
mysql> grant replication slave on *.* to slaveuser@192.168.1.2 identified by '123456' ;
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 | 120 | testdb | |
+------------------+----------+--------------+------------------+
记录下二进制日志的文件名及位置。
2. 从库编辑my.cnf
Mysql代码
[mysqld]
server-id=2 #唯一
#设置要进行或不要进行主从复制的数据库名，同时也要在 Master 上设定。
#replicate-do-db=数据库名1
#replicate-do-db=数据库名2
#replicate-ignore-db=数据库名1
#replicate-ignore-db=数据库名2
replicate-do-db=testdb
重启mysql 服务后，连接数据库。
Mysql代码
mysql> change master to master_host='192.168.1.1',master_user='slaveuser',master_password='123456', master_log_file='mysql-bin.000016',master_log_pos=120;
mysql> start slave;
mysql> show slave status\G;
如显示如下，则成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_Db: testdb
3. Mysql主备复制实现工作原理
从上图来看，复制分成三步：
master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件，binary log events，可以通过show binlog events进行查看);
slave将master的binary log events拷贝到它的中继日志(relay log);
slave重做中继日志中的事件，将改变反映它自己的数据。
附一些问题的解决过程：
1). error：“/usr/sbin/mysqld: unknown variable 'master-host=10.1.4.154'”
原因是MySQL自5.1.7版本之后不支持master-host 类似的参数，需要在从库上执行：
change master to master_host='masterIP', master_user='slave', master_password='slvaePASS';
slave start;
2). slave status: "Slave_IO_Running:No"
Mysql代码
解决方法：
Mysql > stop slave;
Mysql > set global sql_slave_skip_counter =1 ;
Mysql > start slave;
3). Last_IO_Error: Got fatal error 1236 from master when reading data from binary log
对照master status 确认log文件及position数据是否正确。
4). Slave can not handle replication events with the checksum that master is configured to log
这个错误一般出现在master5.6,slave在低版本的情况下。这是由于5.6使用了crc32做binlog的checksum。除了把master的设置从crc32改到none
Mysql代码
binlog-checksum = none [my.cnf]
5). 在SLAVE执行CHANGE MASTER时，出现ERROR 1201 (HY000): Could not initialize master!
Msyql代码
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.100.10',master_user='replication',master_password='123456',master_log_file='updatelog.000004',master_log_pos=106;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
修正问题：
Mysql代码
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected,(0.00 sec)
mysql> change master to master_host='192.168.100.10',master_user='replication',master_password='123456',master_log_file='updatelog.000004',master_log_pos=106;
Query OK, 0 rows affected (0.05 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
6). 主从复制binlog问题，如果没有删除的配置，则会无限增长，严重占用磁盘空间。
Java代码
expire_logs_days = 30 #有效期30天，之前的会被删除
7). 如果主数据库服务器已经存在用户数据，那么在进行主从复制时，需要做以下处理。
Java代码
(1)主数据库锁表操作，不让数据再进行写入动作。
mysql>flush tables with read lock;
(2)查看主数据库的状态
mysql>show master status;
记下File以及Position的值，以备从服务器使用。
(3)把主服务器的数据文件复制到从服务器，最好先用tar归档压缩处理一下
(4)取消主数据库锁定
mysql>unlock tables;
从服务器的操作和前面的步骤一样，略过。
一个优化好的主从数据库配置文件例子
一个优化好后的主数据库配置文件和从数据配置文件内容如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
port=3306
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8
[mysqld]
sync_binlog=1
server-id=1
port=3306
socket=/usr/local/mysql/mysql.sock
pid-file=/home/mysql/temp/my3306.pid
user=mysql
datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
log-bin=/home/mysql/data/mysqlmaster-bin
log-error=/home/mysql/logs/error.log
slow_query_log_file=/home/mysql/logs/slow.log
binlog_format=mixed
slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
character_set_server=utf8
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
expire-logs-days=10
skip-slave-start
skip-name-resolve
lower_case_table_names=1
log_bin_trust_function_creators=1
# InnoDB
innodb_data_home_dir=/home/mysql/data
innodb_log_group_home_dir=/home/mysql/logs
innodb_data_file_path=ibdata1:128M:autoextend
innodb_buffer_pool_size=2G
innodb_log_file_size=10M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_flush_log_at_trx_commit=1
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
一个优化好的从数据库的配置文件如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
port=3306
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8
[mysqld]
sync_binlog=1
server-id=2
port=3306
socket=/usr/local/mysql/mysql.sock
pid-file=/home/mysql/temp/my3306.pid
user=mysql
datadir=/home/mysql/data
tmpdir=/home/mysql/temp/
log-bin=/home/mysql/data/mysqlslave-bin
log-error=/home/mysql/logs/error.log
slow_query_log_file=/home/mysql/logs/slow.log
binlog_format=mixed
slow_query_log
long_query_time=10
wait_timeout=31536000
interactive_timeout=31536000
max_connections=500
max_user_connections=490
max_connect_errors=2
character_set_server=utf8
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
expire-logs-days=10
#skip-slave-start
skip-name-resolve
lower_case_table_names=1
log_bin_trust_function_creators=1
# InnoDB
innodb_data_home_dir=/home/mysql/data
innodb_log_group_home_dir=/home/mysql/logs
innodb_data_file_path=ibdata1:128M:autoextend
innodb_buffer_pool_size=2G
innodb_log_file_size=10M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_flush_log_at_trx_commit=1
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256K
sort_buffer_size = 256K
read_buffer = 256K
write_buffer = 256K
[mysqlhotcopy]
interactive-timeout
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256K
sort_buffer_size = 256K
read_buffer = 256K
write_buffer = 256K
[mysqlhotcopy]
interactive-timeout
846
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
