怎么安装Mysql双机热备
这篇文章主要介绍“怎么安装Mysql双机热备”,在日常操作中,相信很多人在怎么安装Mysql双机热备问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”怎么安装Mysql双机热备”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
一、安装mysql#tar-xfmysql-5.7.18-1.el6.x86_64.rpm-bundle.tar#yumlocalinstall*.rpm1.1修改mysql配置
#Foradviceonhowtochangesettingspleasesee#http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]##Removeleading#andsettotheamountofRAMforthemostimportantdata#cacheinMySQL.Startat70%oftotalRAMfordedicatedserver,else10%.#innodb_buffer_pool_size=128M##Removeleading#toturnonaveryimportantdataintegrityoption:logging#changestothebinarylogbetweenbackups.#log_bin##Removeleading#tosetoptionsmainlyusefulforreportingservers.#TheserverdefaultsarefasterfortransactionsandfastSELECTs.#Adjustsizesasneeded,experimenttofindtheoptimalvalues.#join_buffer_size=128M#sort_buffer_size=2M#read_rnd_buffer_size=2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityriskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmp[client]default-character-set=utf8mb4[mysqld]character_set_server=utf8mb41.2权限修改
[root@172~]#chown-Rmysql:mysql/data[root@172~]#chmod777-R/data/[root@172~]#chmod-R777/tmp1.3启动mysql服务
[root@172~]#servicemysqldrestartStoppingmysqld:[FAILED]InitializingMySQLdatabase:[OK]Installingvalidatepasswordplugin:[OK]Startingmysqld:[OK]1.4查看temp密码
more/var/log/mysqld.log|greptemporary1.5修改root密码db1
ALTERUSER'root'@'localhost'IDENTIFIEDBY'*****';flushprivileges;exit;db2
ALTERUSER'root'@'localhost'IDENTIFIEDBY'*****';flushprivileges;exit;二、配置主从同步master1 172.28.8.187 master2172.28.8.1882.1 配置master1给master2登录的密码
Master1
createuser'repl'identifiedby'*****';GRANTREPLICATIONSLAVEON*.*TO'repl'@'172.28.8.188'IDENTIFIEDBY'*****';FLUSHPRIVILEGES;mysql>createdatabasemydbdefaultcharsetutf8;在172.28.8.188测试repuser是否能登录172.28.8.187上的数据库
mysql-urepl-p-h272.28.8.1872.1.1 Master1配置my.cnf
#Foradviceonhowtochangesettingspleasesee#http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]##Removeleading#andsettotheamountofRAMforthemostimportantdata#cacheinMySQL.Startat70%oftotalRAMfordedicatedserver,else10%.#innodb_buffer_pool_size=128M##Removeleading#toturnonaveryimportantdataintegrityoption:logging#changestothebinarylogbetweenbackups.#log_bin##Removeleading#tosetoptionsmainlyusefulforreportingservers.#TheserverdefaultsarefasterfortransactionsandfastSELECTs.#Adjustsizesasneeded,experimenttofindtheoptimalvalues.#join_buffer_size=128M#sort_buffer_size=2M#read_rnd_buffer_size=2Mdatadir=/data/mysqlsocket=/var/lib/mysql/mysql.sock#Disablingsymbolic-linksisrecommendedtopreventassortedsecurityriskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidexplicit_defaults_for_timestamp=truetmpdir=/tmpcharacter_set_server=utf8mb4server-id=177log-bin=/var/log/mysql/mysql-bin.logread-only=0binlog-ignore-db=mysqlbinlog-ignore-db=information_schemaexpire_logs_days=365auto-increment-increment=2auto-increment-offset=1[client]default-character-set=utf8mb42.2 Master2配置my.cnf
#除server-id外,其他与master1保持一致2.2.1 Master2给Master1创建账号密码并授权
createuser'repl'identifiedby'*****';GRANTREPLICATIONSLAVEON*.*TO'repl'@'172.28.8.187'IDENTIFIEDBY'*****';FLUSHPRIVILEGES;2.3 查看Master同步状态
master1
mysql>showmasterstatus;+------------------+----------+--------------+--------------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+--------------------------+-------------------+|mysql-bin.000001|154|mydb|mysql,information_schema||+------------------+----------+--------------+--------------------------+-------------------+1rowinset(0.00sec)
master2
mysql>showmasterstatus;+------------------+----------+--------------+--------------------------+-------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+------------------+----------+--------------+--------------------------+-------------------+|mysql-bin.000001|154|mydb|mysql,information_schema||+------------------+----------+--------------+--------------------------+-------------------+1rowinset(0.00sec)
设置master1从master2同步
mysql>CHANGEMASTERTOMASTER_HOST='172.28.8.188',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='b4l:GGtG3s0*',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=860;mysql>SHOWSLAVESTATUS\Gmysql>STARTSLAVE;mysql>SHOWSLAVESTATUS\G
设置master2从master1同步
mysql>CHANGEMASTERTOMASTER_HOST='172.28.8.187',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='2S1*8pr+BzqH^8T`',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1497;mysql>SHOWSLAVESTATUS\Gmysql>STARTSLAVE;mysql>SHOWSLAVESTATUS\G
如出现以下两项,则说明配置成功!
Slave_IO_Running:YesSlave_SQL_Running:Yes3.双主同步测试
进入master1 mysql 数据库
mysql>createdatabasecrm;QueryOK,1rowaffected(0.00sec)mysql>usecrm;Databasechangedmysql>createtableemployee(idintauto_increment,namevarchar(10),primarykey(id));QueryOK,0rowsaffected(0.00sec)mysql>insertintoemployee(name)values('a');QueryOK,1rowaffected(0.00sec)mysql>insertintoemployee(name)values('b');QueryOK,1rowaffected(0.00sec)mysql>insertintoemployee(name)values('c');QueryOK,1rowaffected(0.06sec)mysql>select*fromemployee;+----+------+|id|name|+----+------+|1|a||3|b||5|c|+----+------+3rowsinset(0.00sec)
进入master2,查看是否有crm这个数据库和employee表。
mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||crm||mysql||performance_schema|+--------------------+4rowsinset(0.00sec)mysql>usecrm;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+---------------+|Tables_in_crm|+---------------+|employee|+---------------+1rowinset(0.00sec)mysql>select*fromemployee;+----+------+|id|name|+----+------+|1|a||3|b||5|c|+----+------+3rowsinset(0.00sec)mysql>insertintoemployee(name)values('d');QueryOK,1rowaffected(0.00sec)mysql>select*fromemployee;+----+------+|id|name|+----+------+|1|a||3|b||5|c||7|d|+----+------+4rowsinset(0.00sec)
在master1的中查看是否有刚刚在master2中插入的数据。
mysql>select*fromemployee;+----+------+|id|name|+----+------+|1|a||3|b||5|c||7|d|+----+------+4rowsinset(0.00sec)
到此,关于“怎么安装Mysql双机热备”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。