Mysql master/slave replication这个架构既可以支持高可用性双机备份系统的建立,也可以支持通过读写分离提高数据库写入和读出性能。
Master slave replication in mysql offload some queries from one server to other. The best advantage of master slave is to use master for all inserts and send some select queries to slave. This practice may increase speed of your application without going into optimization of all queries or buying more RAM.
This master slave configuration can be done in 3 follow steps like:
First connet to your server via putty or open terminal directly.
步骤一
Step 1: Setup Master configuration file properties
Open my.cnf file:
vi /etc/my.cnf
And comment lines like below:
#skip-networking
#bind-address = 127.0.0.1
we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master)
log-bin = /var/lib/mysql/mysql-bin.log
binlog-do-db=database_name_to_replication#要同步复制的数据库名
server-id=1
restart mysql
service mysqld restart
步骤二
Step 2: Setup Master configuration for replication user
logon to mysql server
mysql
create an user with “replication slave on” privileges:
GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
GRANT RELOAD ON *.* TO 'replication_user'@'%';
GRANT SUPER ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
create replication datebase:
CREATE DATABASE database_name_to_replication;
Flush tables in our database:
USE database_name_to_replication;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
you will see like this:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 106 | db to replicate| mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
==============================================
步骤三
Step 3: Setup Slave configuration file
Open my.cnf file:
vi /etc/my.cnf
And commands lines like below:
server-id=2
master-host = 10.22.4.33#host_to_master_server主数据库ip地址
master-user = replication_user
master-password = password
master-port = 3306
master-connect-retry=60
replicate-do-db=database_name_to_replication
Restart mysql:
service mysqld restart
Run below commands:
STOP SLAVE;
RESET SLAVE;
START SLAVE;
SHOW SLAVE STATUS;
the output will be somthing like this:
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
.............
now we should load data from our master:
LOAD DATA FROM MASTER;
or… you can always use below command to load the initial data from master:(在windows环境下,load data from master似乎不起作用,没办法只能用以下的方法来实现)
mysql -usome_user_name -psome_password -Ddatabase_name_to_replication < dump_from_master_db.sql
after our data is moved to slave server… we can unlock our tables.
Log in to MySQL sever (master) and:
USE database_name_to_replication;
UNLOCK TABLES;
Now just browse your database on master and slave servers. Put some new data on master database, browse the slave database.
Hey you have new data on your slave system.
All Done!
Cheers!!
注意:在做master-slave-replication时,slave端的mysql版本要大于等于master端的mysql版本,最好版本一致。
分享到:
相关推荐
1. 是一个“主从复制”(Maser-Slave Replication)的 MySQL 集群 3. 有多个从节点(Slave) 4. 从节点需要能水平扩展 5
Maser - Web Design Agency Elementor Template Kit Maser - 网络设计代理Elementor模板套件" ---------- 泰森云每天更新发布最新WordPress主题、HTML主题、WordPress插件、shopify主题、opencart主题、PHP项目源码...
安装配置flask之前须先安装flask的必要组件之一werkzeug-maser
maser.pdf.editor.v4.3.61的注册机,linux下可以先打开软件,点击帮助选项卡,点击注册,再选中离线,然后就会出现独一无二的id。如我的是 00000031-0034-0035-3433-36373138322D 。接着,win下打开注册机就可以...
Modbus的ASCII、RTU协议规定了消息、数据的结构、命令和就答的方式,数据通讯采用Maser/Slave方式,Master端发出数据请求消息,Slave端接收到正确消息后就可以发送数据到Master端以响应请求;Master端也可以直接发...
Cyclotron Autoresonance Maser (CARM) Amplifier with Outer-Slotted Coaxial Waveguide,邱春容,张世昌,A self-consistent nonlinear theory is described for a cyclotron autoresonance maser (CARM) ...
Laser atmospheric transmission
用VHDL实现扩频系统的抗窄带干扰功能。
2012年8月15日,英国科学家成功研制出首台可在室温下运行的微波激射器,为其能够被广泛应用铺平了道路。甚至有望给通信和空间探索领域带来革新。据团队带头人马克?奥克斯波罗(Mark Oxborrow)博士称, 这个“新型...
好用的gopher协议工具,两种模式(反弹shell&phpshell),可以自动生成payload,使用payload时请注意再次进行url编码
同轴波导回旋自动谐振脉塞放大器的参量分析,张辉波,张世昌,非线性模拟表明,工作频率为140GHz的同轴波导回旋自动谐振脉塞放大器可以获得65分贝的高增益,输出功率达数百千瓦。电子束的初始半�
激光切割仪上位机程序与Arduino下位机程序。
MBC-Maser MBC-Masr-2 MBC-IRAQ MBC-戏剧 MBC-戏剧+ MBC动作 MBC-MAX MBC宝莱坞 MBC +电源 MBC品种 ART集团 ART-Aflam-1 ART-Aflam-2 电影院 赫卡亚特 ART-Hekayat-2 罗塔纳集团 Rotana-Comedy ...
opencv-maser 深度学习框架安装
2012年第12期本刊发表的《Maser,另一个改变世界的利器》一文中有一张照片[2012, (12):18],细心的读者会发现图片的右边,站着一个亚洲人。他就是和汤斯一起参加了第一台微波激射器(Maser)理论和实验工作的中国科学...
This paper describes a model of electron energization and cyclotron-maser emission applicable to astrophysical magnetized collisionless shocks. It is motivated by the work of Begelman, Ergun and Rees ...
kubernetes 源代码,可以使用源代码编译文件到不同的架构下部署集群,具体使用方法里面有readme文件,也可以搜索相关技术文章 关键词 源码安装xxx
来看一则新闻:2012年8月15日,英国科学家成功研制出首台可在室温下运行的锥波激射器,为其能够被广泛应用铺平了道路,甚至有望给通信和空间探 索领域带来革新。据团队带头人马克·奥克斯波罗(Mark Oxborrow)博士称...
OPTI toolbox 的工具包,可以直接下载解压到文件夹中即可使用
Apache ActiveMQ 5.8 Maser 队列