mysql 主从复制之两台mysql数据之间实现主从复制

mysql 主从复制之两台mysql数据之间实现主从复制

mysql 主从复制之两台mysql数据之间实现主从复制

1.前提

1.假设你已经在两台不同的linux服务器上已经成功的分别安装了一台mysql服务2.如果没有安装成功,可以参考下面的mysql在linux上的安装地址

安装地址 mysql 安装之linux环境安装mysql-通过jar包安装配置 mysql 安装之linux环境命令安装mysql​

2.目标

1.主数据库中的数据变更,同步到从数据库中

3.主从复制配置步骤

3.1.概念自定义

1.主数据库所在服务器:192.168.184.136 我们定义为A2.从数据库所在服务器:192.168.184.135

3.1.分别修改主从数据库的服务server-id

3.1.1.主数据库server-id修改

[root@izwz91h49n3mj8r232gqwez ~]# vim /etc/my-f

# For advice on how to change settings please see# For advice on how to change settings please see# 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## 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#skip-grant-tables# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error=/var/log/mysqld.log

备注: 写入内容如下

log-bin=mysql-bin #[必须]启用二进制日志server-id=135 #[必须]服务器唯一ID，默认是1，一般取IP最后一段

重启mysql服务

[root@izwz91h49n3mj8r232gqwez weblogic]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.service

3.1.2.从数据库server-id修改

从数据库的配置如上,只不过server-id不一样 写入内容如下

log-bin=mysql-bin #[必须]启用二进制日志server-id=136 #[必须]服务器唯一ID，默认是1，一般取IP最后一段

重启mysql服务

[root@izwz91h49n3mj8r232gqwez weblogic]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.service

3.2.在主服务器A上建立帐户并授权slave

3.2.1.主服务器A-linux上首先登陆mysql服务器

[root@izwz91h49n3mj8r232gqwez weblogic]# mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.41-log MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

3.2.2.授权slave(授权从服务器可以进行远程连接同步)

mysql> GRANT REPLICATION SLAVE ON *.* to 'sync'@'192.168.184.136' identified by 'sync'; Query OK, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 330 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

说明

1.GRANT REPLICATION SLAVE ON *.* to 'sync'@'192.168.184.136' identified by 'sync'; 这里的192.168.184.135是可以允许的远程复制的从服务器的ip 第一个sync为远程连接的账户,第二个sync为远程连接的密码, 当然你可以自定义其他的账户名或密码2.show master status;#查看主数据的状态 File :mysql-bin.000001 会记录所有的主数据库更新的sql 结果中的Position:300 为开始复制的位置 , 注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化, 因为,你执行了跟新操作后,这个值会变化

3.3.配置从数据库(配置Salve)

3.3.1.从服务器B-linux上首先登陆mysql服务器

略

3.3.2.mysql命令配置

> change master to master_host='192.168.184.135',master_user='sync',master_password='sync', -> master_log_file='mysql-bin.000001',master_log_pos=330; Query OK, 0 rows affected, 2 warnings (0.01 sec)

备注

1.master_host上一个步骤配置的主数据库的ip 2.master_user 上面允许的账户名3.master_password 上面允许的账户名对应的密码4.master_log_file='mysql-bin.000001' 上面主数据库记录更新sql的文件名5.master_log_pos=330; 上面主数据库更新sql记录的最后的位置(开始同步的位置)

查看从数据库的配置的状态

mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.145.222 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 330 Relay_Log_File: izwz97qxc2ue9zskj6lhwpz-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 330 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

3.3.3.从服务器启动复制

mysql> START SLAVE;Query OK, 0 rows affected (0.00 sec)

至此,在主数据库(A)里进行的数据更新,都会同步到从数据库里面(B)

3.4.进行数据测试

主数据库里执行下面命令

mysql> create database test -> ;Query OK, 1 row affected (0.00 sec)mysql>

从数据库查看是否已经创建成功

> show databases -> ;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test | //已经有了+--------------------+5 rows in set (0.00 sec)mysql>

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。