CentOs MySQL 数据库主从同步配置

缺乏、安全感 2023-10-17 02:56 18阅读 0赞

前提条件：

硬件要求：

1、主从服务器系统版本和版本位数一致

2、MySQL 版本一致。

服务器配置

master:120.76.112.207

slave:120.25.58.50

CentOS 安装MySQL 具体可以参考：[http://blog.csdn.net/zhouzhiwengang/article/details/51701537][http_blog.csdn.net_zhouzhiwengang_article_details_51701537]

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 required.
    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

为MySQL 创建同步用户

\#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)服务器配置

修改mysql 配置文件信息

\#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, 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
    user=mysql
    #datadir = /db/mysql/data
    
    # These are commonly set, remove the # and set as required.
     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, 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
    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',

master\_password='123456',

master\_port=3306,

master\_log\_file='mysql-bin.000003',

master\_log\_pos=2005,

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，则表示连接正常。

此时就可以测试主从复制了

[http_blog.csdn.net_zhouzhiwengang_article_details_51701537]: http://blog.csdn.net/zhouzhiwengang/article/details/51701537