前提条件:
硬件要求:
1、主从服务器系统版本和版本位数一致
2、MysqL 版本一致。
服务器配置
master:120.76.112.207
slave:120.25.58.50
CentOS 安装MysqL 具体可以参考:http://www.jb51.cc/article/p-gfbzfsmj-ty.html
Master服务器配置(120.76.112.207)
编辑MysqL 配置信息
#vi /etc/my.cnf
Welcome to aliyun Elastic Compute Service! [root@iZ94phz01rnZ ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.MysqL.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install,and will be replaced if you # *** upgrade to a newer version of MysqL. [MysqLd] # Remove leading # and set to the amount of RAM for the most important data # cache in MysqL. Start at 70% of total RAM for dedicated server,else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. log_bin = MysqL-bin server-id = 1 user = MysqL #主从复制配置 innodb_flush_log_at_trx_commit=1 sync_binlog=1 #需要备份的数据库 binlog-do-db=test #不需要备份的数据库 binlog-ignore-db=MysqL #不需要备份的数据库 binlog-ignore-db=information_schema #不需要备份的数据库 binlog-ignore-db=performance_schema # These are commonly set,remove the # and set as @R_502_103@. basedir = /usr/local/MysqL datadir = /db/MysqL/data #port =3306 #server_id = 120.76.112.207 #socket = # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed,experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log_error = /var/log/MysqL/error.log #pid_file = /var/lib/MysqL/MysqL.pid #开启查询缓存 #explicit_defaults_for_timestamp=true symbolic-links=0 #innodb_force_recovery=1
提示:binlog-ignore-db 表示不需要备份的数据库 、binlog-do-db表示需要备份数据库,如果两个属性都未配置,那就默认标识同步所有的数据库。
修改配置文件信息(/etc/my.cnf),需要重启MysqL 服务,才会生效。
# /etc/init.d/MysqL restart 或者 #service MysqL restart
#cd /usr/local/MysqL/bin (MysqL 安装目录)
#MysqL -u root -p (MysqL 管理员登入 )
Enter PassWord
创建用户:
MysqL> create user 'mastj'@'192.168.1.16' identified by '123456';
MysqL> grant replication slave on *.* to 'mastj'@'192.168.1.16' identified by '123456';
查看master 状态:
MysqL > show master status;
Slave(120.25.58.50)服务器配置
#vi /etc/my.cnf
[root@iZ94qvmp90hZ ~]# cat /etc/my.cnf # For advice on how to change settings please see # http://dev.MysqL.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install,else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin user=MysqL #datadir = /db/MysqL/data # These are commonly set,remove the # and set as @R_502_103@. basedir = /usr/local/MysqL datadir = /db/MysqL/data # port = ..... # server_id = ..... #socket =/usr/local/MysqL/MysqL.sock # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed,STRICT_TRANS_TABLES log_error = /var/log/MysqL/error.log log_bin = MysqL-bin server-id = 2重新启动MysqL服务
# /etc/init.d/MysqL restart 或者service MysqL restart
执行
MysqL> change master to master_host='192.168.1.18',
master_user='mastj',43); font-family:Arial; font-size:14px; line-height:26px"> master_password='123456',43); font-family:Arial; font-size:14px; line-height:26px"> master_port=3306,43); font-family:Arial; font-size:14px; line-height:26px"> master_log_file='MysqL-bin.000003',43); font-family:Arial; font-size:14px; line-height:26px"> master_log_pos=2005,43); font-family:Arial; font-size:14px; line-height:26px"> master_connect_retry=10;
参数详解:
master_host:主服务器的IP。
master_user:配置主服务器时建立的用户名
master_password:用户密码
master_port:主服务器MysqL端口,如果未曾修改,默认即可
master_log_file:日志文件名称,填写查看master状态时显示的File
master_log_pos:日志位置,填写查看master状态时显示的Positio
master_connect_retry:重连次数
启动数据同步线程
MysqL >start slave;
简称数据同步状态:
MysqL> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.18 Master_User: mastj Master_Port: 3306 Connect_Retry: 10 Master_Log_File: MysqL-bin.000003 Read_Master_Log_Pos: 2369 Relay_Log_File: jhq0113-relay-bin.000002 Relay_Log_Pos: 647 Relay_Master_Log_File: MysqL-bin.000003 Slave_IO_Running: Yes Slave_sql_Running: Yes
若Slave_IO_Running和Slave_sql_Running均为Yes,则表示连接正常。
此时就可以测试主从复制了