Mysql主从服务器部署文档.docx

1、Mysql主从服务器部署文档东莞质监Mysql主从复制安装文档 Mysql主从备份概述 Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然

2、后封锁并等待主服务器通知新的更新。原理：复制有3个步骤：(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；(2) slave将master的binary log events拷贝到它的中继日志(relay log)；(3) slave重做中继日志中的事件，将改变反映它自己的数据一 Linux坏境准备Mysql版本 ：mysql-5.7.10-linux-glibc2.5-x86_64.tar下载地址 :19.106.64.39 master节点 用户root：密码mysql19.106.64.40slave节

3、点 用户root：密码mysqlLinux mysql用户登录密码：mysql1)检查系统是否自带mysql以防安装冲突rootMysql1mysql# rpm -qa|grep -i mysqlmysql-libs-5.1.71-1.el6.x86_64rootMysql1mysql# rpm -e mysql-libs-5.1.71-1.el6.x86_64 nodeps#解除依赖卸载2)新建mysql用户rootMysql1 # useraddmysqlrootMysql1 # passwdmysql #mysqlChanging password for user mysql.root

4、Mysql1 mysql# mkdir data #在mysql文件夹下创建文件夹data3)关闭防火墙service iptables stop #临时关闭chkcongfigiptables off #永久关闭二 安装Mysql1)解压压缩包tar -xzvf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz -C /optrootMysql1 opt# cdmysql/rootMysql1 mysql# mv * /home/mysql/2)初始化rootMysql1 bin# ./mysql_install_db -user=mysql-basedir

5、=/home/mysql-datadir=/home/mysql/data2017-05-04 16:54:01 WARNING mysql_install_db is deprecated. Please consider switching to mysqld -initialize2017-05-04 16:54:07 WARNING The bootstrap log isnt empty:2017-05-04 16:54:07 WARNING 2017-05-04T08:54:01.525625Z 0 Warning -bootstrap is deprecated. Please

6、consider using -initialize instead2017-05-04T08:54:01.526123Z 0 Warning Changed limits: max_open_files: 1024 (requested 5000)2017-05-04T08:54:01.526129Z 0 Warning Changed limits: table_open_cache: 431 (requested 2000)清空Mysqldatadir ;root用户下操作；chownR mysql:mysql /home/mysql;rmrf * /home/mysql/datamys

7、qlMysql1 $ ./bin/mysqld -user=mysql -basedir=/home/mysql -datadir=/home/mysql/data -initialize2017-05-04T09:11:33.288483Z 0 Warning Changed limits: max_open_files: 1024 (requested 5000)2017-05-04T09:11:33.288553Z 0 Warning Changed limits: table_open_cache: 431 (requested 2000)2017-05-04T09:11:33.288

8、687Z 0 Warning TIMESTAMP with implicit DEFAULT value is deprecated. Please use -explicit_defaults_for_timestamp server option (see documentation for more details).2017-05-04T09:11:33.857338Z 0 Warning InnoDB: New log files created, LSN=457902017-05-04T09:11:34.467334Z 0 Warning InnoDB: Creating fore

9、ign key constraint system tables.2017-05-04T09:11:34.500803Z 0 Warning No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab9ed14c-30a9-11e7-a2a1-0cda411d826d.2017-05-04T09:11:34.506874Z 0 Warning Gtid table is not read

10、y to be used. Table mysql.gtid_executed cannot be opened.2017-05-04T09:11:34.507404Z 1 Note A temporary password is generated for rootlocalhost: VDsFAytCu5;Z #初始密码4)启动mysqlmysqlMysql1 $ cd support-files/mysqlMysql1 support-files$ lsmysqlMysql1 support-files$ ./mysql.server start./mysql.server: line

11、256: my_print_defaults: command not found./mysql.server: line 276: cd: /usr/local/mysql: No such file or directoryStarting MySQLCouldnt find MySQL server (/usr/local/mysql/bin/mysqld_safe)FAILEDmysql的tar.gz安装包的默认安装目录为/usr/local/mysql，这时候我们需要修改/support-files/mysql.server文件的basedir和datadir目录路径为我们环境所在的

12、mysql的basedir和datadir路径vi/home/mysql/support-files/mysql.servermysqlMysql1 support-files$ ./mysql.server startStarting MySQL. OK #启动成功rootMysql2 bin# /home/mysql/bin/mysql -urootp#修改root密码：Enter password:mysql set password = password(mysql);Query OK, 0 rows affected, 1 warning (0.01 sec)mysql alter

13、user rootlocalhost password expire never;#密码永不过期Query OK, 0 rows affected (0.00 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)5)配置mysql开机自启动创建软连接（root用户下操作）ln -s /home/mysql/bin/mysql /usr/bin/mysqlrootMysql1 # cp /home/mysql/support-files/mysql.server /etc/init.d/mysqlrootMysql1 #

14、chmod 755 /etc/init.d/mysqlrootMysql1 # chkconfig -add mysqlrootMysql1 # chkconfigmysql onrootMysql1 # chkconfig -list |grepmysqlmysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off rootMysql1 # service mysql statusMySQL running (3637) OK 6)拷贝配置文件到/etc目录下当mysql服务启动的时候，默认会按一定的顺序读取配置文件的。Default options areread

15、from the following filesinthe given order:/etc/f/etc/mysql/f/opt/mysql/etc/f /f当有多个配置文件时，mysql会以读取到的最后一个配置文件中的参数为准。rootMysql1 # cp /home/mysql/support-files/my-f/etc/f#以上步骤适合Master和slave节点安装部署Mysql#三主从复制配置1)修改Master的配置文件rootMysql1 # vim /etc/fmysqldlog_bin=master-bin.log #开启日志服务basedir = /home/mysql

16、datadir = /home/mysql/data server_id=39 #必须设置server_id,保证集群内的唯一性.可以用ip地址最后3位log-slave-updatesinnodb_flush_log_at_trx_commit=1 #所有事务提交必须写入硬盘sync_binlog=1 #必须同步bin日志binlog_format=mixedmax_connections=1000relay-log=master-relay-binmaster-info-repository=tablerelay-log-info-repository=tablerelay-log-rec

17、overy=1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp=true2)修改Slave的配置文件rootMysql2 support-files# vi /etc/fmysqldlog_bin=slave-bin.logbasedir = /home/mysqldatadir = /home/mysql/data server_id =40innodb_flush_log_at_trx_commit=1log-slave-updatessync_binlog=1binlog

18、_format=mixedmax_connections=1000relay-log=slave-relay-binmaster-info-repository=tablerelay-log-info-repository=tablerelay-log-recovery=1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp=true修改配置文件后重新启动mysqlmysql service restart3)Mater节点创建复制权限用户mysql grant replicati

19、on slave,reload,super on *.* to repl_userslave identified by repl_password;Query OK, 0 rows affected, 1 warning (0.02 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)4)slave节点创建replication连接查看master节点的状态信息Slave节点操作change master to master_host=19.106.64.39,master_user=repl_user,master_

20、password=repl_password,master_log_file=master-bin.000001,master_log_pos=613;5)检验mysql show slave status G; -slave节点执行* 1. row *Slave_IO_State: Waiting for master to send eventMaster_Host: 19.106.64.39Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-bin.000005Read_Master_Log

21、_Pos: 154Relay_Log_File: slave-relay-bin.000004Relay_Log_Pos: 369Relay_Master_Log_File: master-bin.000005Slave_IO_Running: YesSlave_SQL_Running: Yes #这两个线程必须成功启动Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Las

22、t_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 1204Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Ce

23、rt: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 39Master_UUID: ab9ed14c-30a9-11e7-a2a1-0cda411d826dMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log;

24、 waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)如果slave连接不上masterSlave_IO_Running: connecting状态可关闭防火墙；Master节点查看验证可以在master端建立test库，查看是否slave端同步；