Mysql主从服务器部署文档.docx
《Mysql主从服务器部署文档.docx》由会员分享,可在线阅读,更多相关《Mysql主从服务器部署文档.docx(9页珍藏版)》请在冰豆网上搜索。
![Mysql主从服务器部署文档.docx](https://file1.bdocx.com/fileroot1/2022-11/25/f2e9ae6b-a8a0-46f3-975e-10f55547607c/f2e9ae6b-a8a0-46f3-975e-10f55547607c1.gif)
Mysql主从服务器部署文档
东莞质监Mysql主从复制安装文档
Mysql主从备份概述
Mysql内建的复制功能是构建大型,高性能应用程序的基础。
将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。
复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。
主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。
这些日志可以记录发送到从服务器的更新。
当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。
从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。
原理:
复制有3个步骤:
(1) master将改变记录到二进制日志(binarylog)中(这些记录叫做二进制日志事件,binarylogevents);
(2) slave将master的binarylogevents拷贝到它的中继日志(relaylog);
(3) slave重做中继日志中的事件,将改变反映它自己的数据
一.Linux坏境准备
Mysql版本:
mysql-5.7.10-linux-glibc2.5-x86_64.tar
下载地址:
19.106.64.39master节点用户root:
密码mysql
19.106.64.40slave节点用户root:
密码mysql
Linuxmysql用户登录密码:
mysql
1)检查系统是否自带mysql以防安装冲突
[root@Mysql1mysql]#rpm-qa|grep-imysql
mysql-libs-5.1.71-1.el6.x86_64
[root@Mysql1mysql]#rpm-emysql-libs-5.1.71-1.el6.x86_64–nodeps#解除依赖卸载
2)新建mysql用户
[root@Mysql1~]#useraddmysql
[root@Mysql1~]#passwdmysql#mysql
Changingpasswordforusermysql.
[root@Mysql1mysql]#mkdirdata#在mysql文件夹下创建文件夹data
3)关闭防火墙
serviceiptablesstop#临时关闭
chkcongfigiptablesoff#永久关闭
二.安装Mysql
1)解压压缩包
tar-xzvfmysql-5.7.10-linux-glibc2.5-x86_64.tar.gz-C/opt
[root@Mysql1opt]#cdmysql/
[root@Mysql1mysql]#mv*/home/mysql/
2)初始化
[root@Mysql1bin]#./mysql_install_db--user=mysql--basedir=/home/mysql--datadir=/home/mysql/data
2017-05-0416:
54:
01[WARNING]mysql_install_dbisdeprecated.Pleaseconsiderswitchingtomysqld--initialize
2017-05-0416:
54:
07[WARNING]Thebootstraplogisn'tempty:
2017-05-0416:
54:
07[WARNING]2017-05-04T08:
54:
01.525625Z0[Warning]--bootstrapisdeprecated.Pleaseconsiderusing--initializeinstead
2017-05-04T08:
54:
01.526123Z0[Warning]Changedlimits:
max_open_files:
1024(requested5000)
2017-05-04T08:
54:
01.526129Z0[Warning]Changedlimits:
table_open_cache:
431(requested2000)
清空Mysqldatadir;root用户下操作;chown–Rmysql:
mysql/home/mysql;
rm–rf*/home/mysql/data
[mysql@Mysql1~]$./bin/mysqld--user=mysql--basedir=/home/mysql--datadir=/home/mysql/data--initialize
2017-05-04T09:
11:
33.288483Z0[Warning]Changedlimits:
max_open_files:
1024(requested5000)
2017-05-04T09:
11:
33.288553Z0[Warning]Changedlimits:
table_open_cache:
431(requested2000)
2017-05-04T09:
11:
33.288687Z0[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).
2017-05-04T09:
11:
33.857338Z0[Warning]InnoDB:
Newlogfilescreated,LSN=45790
2017-05-04T09:
11:
34.467334Z0[Warning]InnoDB:
Creatingforeignkeyconstraintsystemtables.
2017-05-04T09:
11:
34.500803Z0[Warning]NoexistingUUIDhasbeenfound,soweassumethatthisisthefirsttimethatthisserverhasbeenstarted.GeneratinganewUUID:
ab9ed14c-30a9-11e7-a2a1-0cda411d826d.
2017-05-04T09:
11:
34.506874Z0[Warning]Gtidtableisnotreadytobeused.Table'mysql.gtid_executed'cannotbeopened.
2017-05-04T09:
11:
34.507404Z1[Note]Atemporarypasswordisgeneratedforroot@localhost:
VDsFAytCu5;Z#初始密码
4)启动mysql
[mysql@Mysql1~]$cdsupport-files/
[mysql@Mysql1support-files]$ls
[mysql@Mysql1support-files]$./mysql.serverstart
./mysql.server:
line256:
my_print_defaults:
commandnotfound
./mysql.server:
line276:
cd:
/usr/local/mysql:
Nosuchfileordirectory
StartingMySQLCouldn'tfindMySQLserver(/usr/local/mysql/bin/mysqld_safe)[FAILED]
mysql的tar.gz安装包的默认安装目录为/usr/local/mysql,这时候我们需要修改/support-files/mysql.server文件的basedir和datadir目录路径为我们环境所在的mysql的basedir和datadir路径
vi/home/mysql/support-files/mysql.server
[mysql@Mysql1support-files]$./mysql.serverstart
StartingMySQL.[OK]#启动成功
[root@Mysql2bin]#/home/mysql/bin/mysql-uroot–p#修改root密码:
Enterpassword:
mysql>setpassword=password('mysql');
QueryOK,0rowsaffected,1warning(0.01sec)
mysql>alteruser'root'@'localhost'passwordexpirenever;#密码永不过期
QueryOK,0rowsaffected(0.00sec)
mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)
5)配置mysql开机自启动
创建软连接(root用户下操作)
ln-s/home/mysql/bin/mysql/usr/bin/mysql
[root@Mysql1~]#cp/home/mysql/support-files/mysql.server/etc/init.d/mysql
[root@Mysql1~]#chmod755/etc/init.d/mysql
[root@Mysql1~]#chkconfig--addmysql
[root@Mysql1~]#chkconfigmysqlon
[root@Mysql1~]#chkconfig--list|grepmysql
mysql0:
off1:
off2:
on3:
on4:
on5:
on6:
off[root@Mysql1~]#servicemysqlstatus
MySQLrunning(3637)[OK]
6)拷贝配置文件到/etc目录下
当mysql服务启动的时候,默认会按一定的顺序读取配置文件的。
Defaultoptionsare read fromthefollowingfiles in thegivenorder:
/etc/f /etc/mysql/f /opt/mysql/etc/f~/f
当有多个配置文件时,mysql会以读取到的最后一个配置文件中的参数为准。
[root@Mysql1~]#cp/home/mysql/support-files/my-f/etc/f
#######以上步骤适合Master和slave节点安装部署Mysql###########
三.主从复制配置
1)修改Master的配置文件
[root@Mysql1~]#vim/etc/f
[mysqld]
log_bin=master-bin.log#开启日志服务
basedir=/home/mysql
datadir=/home/mysql/data
server_id=39#必须设置server_id,保证集群内的唯一性.可以用ip地址最后3位
log-slave-updates
innodb_flush_log_at_trx_commit=1#所有事务提交必须写入硬盘
sync_binlog=1#必须同步bin日志
binlog_format=mixed
max_connections=1000
relay-log=master-relay-bin
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
2)修改Slave的配置文件
[root@Mysql2support-files]#vi/etc/f
[mysqld]
log_bin=slave-bin.log
basedir=/home/mysql
datadir=/home/mysql/dataserver_id=40
innodb_flush_log_at_trx_commit=1
log-slave-updates
sync_binlog=1
binlog_format=mixed
max_connections=1000
relay-log=slave-relay-bin
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp=true
修改配置文件后重新启动mysql
mysqlservicerestart
3)Mater节点创建复制权限用户
mysql>grantreplicationslave,reload,superon*.*torepl_user@slaveidentifiedby'repl_password';
QueryOK,0rowsaffected,1warning(0.02sec)
mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)
4)slave节点创建replication连接
查看master节点的状态信息
Slave节点操作
changemastertomaster_host='19.106.64.39',
master_user='repl_user',
master_password='repl_password',
master_log_file='master-bin.000001',
master_log_pos=613;
5)检验
mysql>showslavestatus\G;--slave节点执行
***************************1.row***************************
Slave_IO_State:
Waitingformastertosendevent
Master_Host:
19.106.64.39
Master_User:
repl
Master_Port:
3306
Connect_Retry:
60
Master_Log_File:
master-bin.000005
Read_Master_Log_Pos:
154
Relay_Log_File:
slave-relay-bin.000004
Relay_Log_Pos:
369
Relay_Master_Log_File:
master-bin.000005
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:
154
Relay_Log_Space:
1204
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:
39
Master_UUID:
ab9ed14c-30a9-11e7-a2a1-0cda411d826d
Master_Info_File:
mysql.slave_master_info
SQL_Delay:
0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State:
Slavehasreadallrelaylog;waitingformoreupdates
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:
1rowinset(0.00sec)
如果slave连接不上masterSlave_IO_Running:
connecting状态可关闭防火墙;
Master节点查看
验证可以在master端建立test库,查看是否slave端同步;