MySQL搭建与主从复制配置

发表于 | 浏览 次
字数统计: 1,824 字 | 阅读时长 ≈ 9 分钟

什么是主从复制：

顾名思义就是指复制一个或多个和主库完全一样的数据库环境，当然我们也称其为从数据库，多个数据库备份不仅可以加强数据的安全性，通过其他手段（例如：读写分离）还能提升数据库的负载性能。
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

使用场景：

  1. 作为后备数据库，当主数据库服务器出现故障后，可切换到从数据库继续工作；
  2. 可以在从数据库作备份、数据统计等工作，这样不影响主数据库的性能(当然建立主从复制本身就是一个备份)；
  3. 减轻主库负载，可以在一些实时性要求并不太高的情况建立读写分离，主库为写，从库为读。

安装约定：

172.16.47.144 master
172.16.47.143 salve
程序版本：5.6.38
安装目录：/application/mysql-5.6.38
数据目录：/application/mysql-5.6.38/data
软件包存放目录：/tools
下载链接：https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.38.tar.gz

安装MySQL：

将源码包从服务器上下载到指定目录并解压：
[root@mysql-master tools]# wget https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.38.tar.gz
[root@mysql-master tools]# ls
mysql-5.6.38.tar.gz
[root@mysql-master tools]#tar –zxvf mysql-5.6.38.tar.gz
[root@mysql-master tools]#ls
mysql-5.6.38 mysql-5.6.38.tar.gz

#创建mysql用户和用户组：
[root@mysql-master tools]# groupadd mysql
[root@mysql-master tools]# useradd mysql -s /bin/nologin -M -g mysql

安装mysql及所依赖的软件包
yum install -y autoconf automake imake libxml2-devel expat-devel cmake gcc gcc-c++ libaio libaio-devel bzr bison ncurses-devel
[root@mysql-master tools]#cd mysql-5.6.38
[root@mysql-master mysql-5.6.38]# cmake \
-DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.38 \
-DMYSQL_DATADIR=/application/mysql-5.6.38/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.38/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0 \

//此为安装成功信息
– Googletest was not found. gtest-based unit tests will be disabled. You can run cmake . -DENABLE_DOWNLOADS=1 to automatically download and build required components from source.
– If you are inside a firewall, you may need to use an https proxy: export https_proxy=http://example.com:80
– Library mysqlserver depends on OSLIBS -lpthread;m;crypt;dl;aio
– Skipping deb packaging on unsupported platform .
– CMAKE_BUILD_TYPE: RelWithDebInfo
– COMPILE_DEFINITIONS: HAVE_CONFIG_H
– CMAKE_C_FLAGS: -Wall -Wextra -Wformat-security -Wvla -Wwrite-strings -Wdeclaration-after-statement
– CMAKE_CXX_FLAGS: -Wall -Wextra -Wformat-security -Wvla -Woverloaded-virtual -Wno-unused-parameter
– CMAKE_C_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
– CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
– Configuring done
– Generating done
– Build files have been written to: /home/macro/tools/mysql-5.6.38

[root@mysql-master mysql-5.6.38]# make

//此为安装成功信息
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir///client/completion_hash.cc.o
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir///client/mysql.cc.o
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir///client/readline.cc.o
Linking CXX executable mysql_embedded
[100%] Built target mysql_embedded
Scanning dependencies of target mysqltest_embedded
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysqltest_embedded.dir///client/mysqltest.cc.o
Linking CXX executable mysqltest_embedded
[100%] Built target mysqltest_embedded
Scanning dependencies of target my_safe_process
[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process
[100%] Built target my_safe_process [root@test mysql-5.6.38]# make install

[root@mysql-master mysql-5.6.38]# make install
[root@mysql-master mysql-5.6.38]# cd /usr/local/mysql/support-files
[root@mysql-master support-files]# cp my-default.cnf /etc/my.cnf
cp：是否覆盖”/etc/my.cnf”？ y
[root@mysql-master support-files]# echo ‘export PATH=/usr/local/mysql/bin:$PATH’ >> /etc/profile
[root@mysql-master support-files]# tail -1 /etc/profile
[root@mysql-master support-files]# source /etc/profile
[root@mysql-master support-files]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mysql-master support-files]# cd ../scripts/
[root@mysql-master scripts]# ./mysql_install_db –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –user=mysql
[root@mysql-master scripts]# cd ../support-files/
[root@mysql-master scripts]# cp mysql.server /etc/init.d/mysqld
[root@mysql-master scripts]# chmod +x /etc/init.d/mysqld
[root@mysql-master scripts]# service mysqld start
Starting MySQL………………………………………… SUCCESS!
[root@mysql-master scripts]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 29745 mysql 10u IPv6 63464 0t0 TCP *:mysql (LISTEN)
[root@mysql-master scripts]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.38 Source distribution

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>

//到此为止mysql的编译安装就到此为止，两台mysql服务器按照以上步骤安装即可，下面开始配置主从复制

主从复制配置：

[root@mysql-master scripts]# vi /etc/my.cnf \master主机
server_id = 144 \服务器唯一ID，默认是1，一般都取ip地址最后一段
log_bin = mysql-bin \启用二进制日志
[root@mysql-slave scripts]# vi /etc/my.cnf \slave主机
server_id = 143
log_bin = mysql-bin

#修改完配置文件后将两台主机的mysql重启让服务加载新的配置；
[root@mysql-master scripts]# service mysqld restart
[root@mysql-slave scripts]# service mysqld restart

#master授权：
mysql> grant replication slave on . to rep@’172.16.47.143’ identified by ‘123456’; \授权172.16.47.143允使用rep用户登陆
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; \刷新系统权限
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+——+—————+
| user | host |
+——+—————+
| root | 127.0.0.1 |
| rep | 172.16.47.143 |
| root | ::1 |
| | localhost |
| root | localhost |
| | mysql-master |
| root | mysql-master |
+——+—————+
7 rows in set (0.00 sec)

mysql> show grants for rep@’172.16.47.143’;
+—————————————————————————————————————————-+
| Grants for rep@172.16.47.143 |
+—————————————————————————————————————————-+
| GRANT REPLICATION SLAVE ON . TO ‘rep’@’172.16.47.143’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
+—————————————————————————————————————————-+
1 row in set (0.00 sec)

mysql> flush tables with read lock; \进行锁表，防止数据不一致
Query OK, 0 rows affected (0.00 sec)

mysql> show master status; \查看master状态
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 120 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

#Slave主机配置：
mysql> change master to MASTER_HOST=’172.16.47.144’,MASTER_USER=’rep’,MASTER_PASSWORD=’123456’,MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.47.144
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
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: 120
Relay_Log_Space: 462
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: 144
Master_UUID: 78a383ac-db4d-11e7-ae39-000c29fdc270
Master_Info_File: /application/mysql-5.6.38/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)

#在master主机上写入数据在slave主机上是否同步：
mysql> unlock tables; \别忘了要解锁表喔
Query OK, 0 rows affected (0.00 sec)

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

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| testmaster_slave |
+——————–+
5 rows in set (0.00 sec)

#在slave主机上查看结果：
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| testmaster_slave |
+——————–+
5 rows in set (0.00 sec)

//到此为止mysql的安装到一主一从复制就完成了；