mysql配置主从

centos6.5
mysql：5.6

主从配置注意点

二进制日志管理
    每个二进制日志大小： max-binlog-size=bytes
    二进制日志保留时间： expire-logs-days=days
    复制格式： binlog_format = MIXED 
    同步日志的事务数： sync-binlog= num
出错后的处理
    跳过

1. 安装mysql
http://blog.itpub.net/28282660/viewspace-1571384/

2.mysql的启动与关闭
[root@root home]# sudo service mysqld start
正在启动 mysqld：[确定]
[root@root home]# sudo service mysqld status
mysqld (pid  16573) 正在运行
[root@root home]# sudo service mysqld stop
停止 mysqld:  [确定]

3.编辑my.cnf文件并保存

不清楚位置可以通过查找配置文件位置
find / -name my.cnf

master 关闭服务编辑my.cnf
sudo service mysqld stop
vim /etc/my.cnf

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

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

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


[mysqld]
log-bin=master-bin
log-bin-index=master-bin.index
port=3306
server-id=100
tmpdir=/tmp

4.启动mysqld服务为master创建数据库快照
sudo service mysqld start
开启另外一个会话执行以下语句
FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS

[root@qingmeng001 ~]# mysqldump --all-databases --master-data >/home/20150511dbdump.db
执行完成后退出开启会话的锁释放锁

5.创建一个用于replication的用户，并授权
mysql> create user 'rep1' identified by 'rep1';
Query OK, 0 rows affected (0.04 sec)

mysql> grant replication slave on *.* to rep1;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

6.slave关闭服务编辑my.cnf

# 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
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
tmpdir=/tmp
server-id=101
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

slave的server-id与master server-id 不同

启动slave数据库

7. 拷贝导出的数据快照到slave，并导入
[root@qingmeng001 home]# scp /home/20150511dbdump.db root@192.168.1.110:/home
root@192.168.1.110's password: 
20150511dbdump.db                                                    100%  613KB 612.7KB/s   00:01    


mysql -h localhost

8.执行以下语句，开始复制
master 执行SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |      405 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


master-bin.000003位置目录/var/lib/mysql

file 对应 MASTER_LOG_FILE
position 对应 MASTER_LOG_POS
执行下列语句

CHANGE MASTER TO
MASTER_HOST='192.168.1.4',
MASTER_USER='rep1',
MASTER_PASSWORD='rep1',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=317;

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;


9.启动slave
start slave;


10.查看slave状态
mysql> show slave status \G


11.测试
master 执行一下语句
mysql> create database test;
Query OK, 1 row affected (0.00 sec)


mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table t (id varchar(20));
Query OK, 0 rows affected (0.07 sec)


mysql> insert into t values(1);
Query OK, 1 row affected (0.03 sec)


mysql> select * from t;
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)


slave查看是否同步


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)


mysql> select * from t;
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)


针对slave多列的 在插入的时候指定对应列


insert into t value(1,1,2);
insert into t(c1,c2,c3) value(1,1,4);


查看当前正在写入的二进制日志文件。
mysql> SHOW MASTER STATUS \G
*************************** 1. row ***************************
             File: master-bin.000005
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

12.reset master 和 reset slave
reset master：删除了所有二进制日志文件并清空二进制日志索引文件。
reset slave：删除slave复制所用的所有文件，并重新开始。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28282660/viewspace-1701277/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28282660/viewspace-1701277/