3
Centos 7下配置MySQL Server5.7主从同步
source link: https://www.daguanren.cc/post/sync_master_slave_mysql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Step 1 — Master数据库服务器配置
[root@server155 ~]# nano /etc/my.cnf
GNU nano 2.0.9 File: /etc/my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[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_binlog-bin=mysql-binserver-id=155binlog-ignore-db=information_schema#binlog-ignore-db=sysbinlog-ignore-db=performance_schemabinlog-ignore-db=mysqlbinlog-do-db=daguanrendb# 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 = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidshell
这里的server-id用于标识唯一的数据库，这里设置为155，在设置从库的时候就需要设置为其他值。
binlog-ignore-db：表示同步的时候ignore的数据库
binlog-do-db：指定需要同步的数据库
Step 2 — Slave数据库服务器配置
[root@server156 ~]# nano /etc/my.cnf
GNU nano 2.0.9 File: /etc/my.cnf # For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[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_binlog-bin=mysql-binserver-id=156binlog-ignore-db=information_schema#binlog-ignore-db=sysbinlog-ignore-db=performance_schemabinlog-ignore-db=mysqlreplicate-do-db=daguanrendbreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allslave-net-timeout=60routeros
Step 3 — 重启主从数据库
[root@server155 ~]# service mysqld restart[root@server156 ~]# service mysqld restart
Step 4 — 进入主数据库，赋予从库权限
[root@server155 ~]# mysql -u root -p
赋予从库权限帐号，允许用户在主库上读取日志，赋予10.0.1.156也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。
mysql> GRANT FILE ON *.* TO 'root'@'10.0.1.156' IDENTIFIED BY 'Daguanren.cc@2017';mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.0.1.156' IDENTIFIED BY 'Daguanren.cc@2017';mysql> flush privileges;
Step 5 — 显示主库信息
mysql> show master status;
这里的 File 、Position 是在配置Salve的时候要使用到的，Binlog_Do_DB表示要同步的数据库，Binlog_Ignore_DB 表示Ignore的数据库，这些都是在配置的时候进行指定的。
Step 6 — 进入从数据库，配置master
[root@server156 ~]# mysql -u root -p
mysql> stop slave;
mysql> change master to master_host='10.0.1.155',master_user='root',master_password='Daguanren.cc@2017',master_log_file='mysql-bin.000003', master_log_pos=725;
mysql> start slave;
在这里指定Master的信息，master_log_file是在配置Master的时候的File选项， master_log_pos是在配置Master的Position 选项，这里要进行对应。
mysql> show slave status;
Step 7 — 测试
创建数据库：
mysql> CREATE DATABASE IF NOT EXISTS iso default charset utf8 COLLATE utf8_general_ci;
在主服务器中：
mysql> use daguanrendb;
mysql> CREATE TABLE test3id intgams
mysql> INSERT INTO test3 VALUES ('11');
mysql> select * from test3;+------++------++------+1 row in set (0.00 sec)asciidoc
在从服务器中：
mysql> use daguanrendb;
mysql> show tables;+---------------------+| Tables_in_daguanrendb |+---------------------+| test3 |+---------------------+1 row in set (0.00 sec)asciidoc
mysql> select * from test3;+------++------++------+1 row in set (0.00 sec)asciidoc
安装MySQL 可参考：Centos 7安装MySQL Server5.7
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK