MySQL复制的优点:
1.如果主服务器出现问题,可以快速切换到从服务器提供的服务
2.可以在从服务器上执行查询操作,降低主服务器的访问压力
3.可以在从服务器上执行备份,以避免备份期间影响主服务器的服务
注意:一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询,实时性要求高的数据仍然需要从主服务器获得。
操作系统 :Windows 7 32位操作系统(安装双数据库端口分别为3306、3308)
数据库版本:MySQL 5.7.18
主机A:192.168.1.103 (Master)
主机B:192.168.1.103(Slave)
配置的设置同样适用于CentOS,Centos的配置文件/etc/my.cnf
- service mysqld stop #停止数据库
- service mysqld start #启动数据库
- service mysqld restart #重启数据库
可以参考:安装MySQL并修改初始密码 http://www.linuxidc.com/Linux/2017-11/148521.htm
这里贴一下Slave数据库安装的日志
- Microsoft Windows [版本 6.1.7601]
- 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
- C:\Windows\system32>cd C:\Program Files\mysql-5.7.18-win32-slave\bin
- C:\Program Files\mysql-5.7.18-win32-slave\bin>mysqld --initialize
- C:\Program Files\mysql-5.7.18-win32-slave\bin>cd ../data
- C:\Program Files\mysql-5.7.18-win32-slave\data>TYPE Javen-PC.err
- 2017-06-29T02:41:51.068120Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is
- deprecated. Please use --explicit_defaults_for_timestamp server option (see doc
- umentation for more details).
- 2017-06-29T02:41:51.573560Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2017-06-29T02:41:51.643760Z 0 [Warning] InnoDB: Creating foreign key constraint
- system tables.
- 2017-06-29T02:41:51.699920Z 0 [Warning] No existing UUID has been found, so we a
- ssume that this is the first time that this server has been started. Generating
- a new UUID: 818f5c2f-5c74-11e7-8dff-000c29b2597f.
- 2017-06-29T02:41:51.699920Z 0 [Warning] Gtid table is not ready to be used. Tabl
- e 'mysql.gtid_executed' cannot be opened.
- 2017-06-29T02:41:51.713960Z 1 [Note] A temporary password is generated for root@
- localhost: =rc%=eBVg0AY
- C:\Program Files\mysql-5.7.18-win32-slave\data>cd ..
- C:\Program Files\mysql-5.7.18-win32-slave>cd bin
- C:\Program Files\mysql-5.7.18-win32-slave\bin>mysqld -install MySQL2
- Service successfully installed.
- C:\Program Files\mysql-5.7.18-win32-slave\bin>net start MySQL2
- MySQL2 服务正在启动 .
- MySQL2 服务已经启动成功。
- C:\Program Files\mysql-5.7.18-win32-slave\bin>mysql -u root -p=rc%=eBVg0AY -P3308
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.7.18
- Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
- Query OK, 0 rows affected (0.00 sec)
- mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT
- OPTION;
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- mysql> FLUSH PRIVILEGES;
- Query OK, 0 rows affected (0.00 sec)
- mysql> exit
- Bye
1、在mysql-5.7.18-win32-slave根目录中创建my.ini初始化配置文件
- [mysqld]
- # set basedir to your installation path
- basedir=C:\\Program Files\\mysql-5.7.18-win32-slave
- # set datadir to the location of your data directory
- datadir=C:\\Program Files\\mysql-5.7.18-win32-slave\\data
- port = 3308
2、进入bin目录mysqld –initialize初始化数据库文件
3、TYPE Javen-PC.err 查看处理初始化密码。
.err 是你电脑的名称 ,这里的初始化密码为
- Javen-PC
4、注册mysql服务 mysqld -install MySQL2
- =rc%=eBVg0AY
- ALTER
- USER
- 'root'
- @
- 'localhost'
- IDENTIFIED
- BY
- 'root'
- ;
8、授权远程登录(需要关闭防火墙或者配置指定端口可以访问)
- GRANT
- ALL
- PRIVILEGES
- ON
- *.*
- TO
- 'root'
- @
- '%'
- IDENTIFIED
- BY
- 'root'
- WITH
- GRANT
- OPTION
- ;
- FLUSH PRIVILEGES;
建立一个帐户javen,并且只能允许从192.168.1.103这个地址上来登陆,密码是123456。
- mysql > grant replication slave on * . * to 'javen'@'192.168.1.103'identified by '123456';
- mysql > flush privileges;
- [mysqld]
- # set basedir to your installation path
- basedir=C:\\Program Files\\mysql-5.7.18-win32
- # set datadir to the location of your data directory
- datadir=C:\\Program Files\\mysql-5.7.18-win32\\data
- port = 3306
- log-bin = mysql-bin #[必须]启用二进制日志
- server-id = 1 #[必须]服务器唯一ID,默认是1
- expire-logs-days = 7 #只保留7天的二进制日志,以防磁盘被日志占满
- #replicate-do-db = test #需要做复制的数据库名;这里不设置只配置备份的数据库
- binlog-ignore-db = mysql #不备份的数据库
- binlog-ignore-db = information_schema
- binlog-ignore-db = performation_schema
- binlog-ignore-db = sys
- #binlog-do-db=test #需要做复制的数据库名
- net stop MySQL
- net start MySQL
在主服务器上设置读取锁定有效,确保没有数据库操作,以便获得一个一致性的快照
- mysql -u root -proot -P3306
- mysql> flush tables with read lock;
- mysql> show master status;
- +------------------+----------+--------------+----------------------------------
- ----------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB
- | Executed_Gtid_Set |
- +------------------+----------+--------------+----------------------------------
- ----------------+-------------------+
- | mysql-bin.000001 | 2519 | | mysql,information_schema,performa
- tion_schema,sys | |
- +------------------+----------+--------------+----------------------------------
- ----------------+-------------------+
- 1 row in set (0.00 sec)
这里的 File 、Position 是在配置Salve的时候要使用到的,Binlog_Do_DB表示要同步的数据库,Binlog_Ignore_DB 表示Ignore的数据库,这些都是在配置的时候进行指定的。
另外:如果执行这个步骤始终为Empty set(0.00 sec),那说明前面的my.init没配置对。
- [mysqld]
- # set basedir to your installation path
- basedir=C:\\Program Files\\mysql-5.7.18-win32-slave
- # set datadir to the location of your data directory
- datadir=C:\\Program Files\\mysql-5.7.18-win32-slave\\data
- port = 3308
- log-bin=mysql-bin
- server-id=3
- binlog-ignore-db = mysql #不备份的数据库
- binlog-ignore-db = information_schema
- binlog-ignore-db = performation_schema
- binlog-ignore-db = sys
- log-slave-updates
- slave-skip-errors=all
- slave-net-timeout=60
- net stop MySQL2
- net start MySQL2
登录从数据库并做如下设置
- mysql> stop slave; #关闭Slave
- mysql> change master to master_host='192.168.1.103',master_user='javen',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos= 2519;
- mysql> start slave; #开启Slave
注意:在这里指定Master的信息,master_log_file是在配置Master的时候的File选项, master_log_pos是在配置Master的Position 选项,这里要进行对应。
show slave status 查看配置的信息:
- mysql> show slave status \G;
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.1.103
- Master_User: mysync
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000001
- Read_Master_Log_Pos: 1192
- Relay_Log_File: Javen-PC-relay-bin.000002
- Relay_Log_Pos: 320
- Relay_Master_Log_File: mysql-bin.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB: mysql
- 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: 1192
- Relay_Log_Space: 530
- 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: 0
- Master_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: 1
- Master_UUID: 818f5c2f-5c74-11e7-8dff-000c29b2597f
- Master_Info_File: C:\Program Files\mysql-5.7.18-win32-slave\data\ma
- ster.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
- dates
- 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)
- ERROR:
- No query specified
- mysql>
- mysql > unlock tables;
在主数据库(Master)添加一个test数据库并在其中添加t1的数据表。如下图
在从数据库(Slave)自动同步,如下图
如有问题欢迎留言….
来源: http://www.linuxidc.com/Linux/2017-11/148520.htm