mysql主从配置
首先可以参考我原来写的mysql在Linux(CentOS)下主从配置详解,在此基础之上加一些简单的操作就可以配置成功啦。
mysql
配置过程
以下称呼的主库和从库都是原始配置主从的主库和从库
- 将从库像主库一样建立
binlog文件夹并在/etc/my.cnf下配置主库信息，mysqld下面大概是这些信息，仅供参考。
[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
# These are commonly set, remove the # and set as required.
# basedir = .....
datadir = /data/mysql
port = 3306
character-set-server=utf8
# server_id = .....
socket = /data/mysql/mysql.sock
server-id=102
relay-log=/data/mysql_slave/relaylog/mysql-relay-bin.log
relay-log-index=/data/mysql_slave/relaylog/mysql-relay-bin.index
relay_log_purge=on
log-bin=/data/mysql_master/binlog/mysql-bin.log
log-bin-index=/data/mysql_master/binlog/mysql-bin.index
read-only=on
同样主库也去像从库一样配置这些信息，这里就不赘述了，接下来都重启一下。
# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
- 查看从库的master运行状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 在从库中添加slave用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'your-slave-ip' IDENTIFIED BY 'your-password';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; +-------------------------------------+
| query |
+-------------------------------------+
| User: 'root'@'%'; |
| User: 'slave'@'your-slave-ip'; |
| User: 'root'@'127.0.0.1'; |
| User: 'root'@'::1'; |
| User: 'root'@'vm\_61\_211\_centos'; |
+-------------------------------------+
5 rows in set (0.00 sec)
- 主库中导入
mysql> change master to master_host='119.29.105.144',master_user='slave',master_password='dsg-password',master_port=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;
mysql> slave start;
mysql> show slave status\G;
像原来配置主从一样成功便是成功。
如果连接不上，别忘了将从库的防火墙打开。
# service firewalld stop
