MySQL架构优化
一、MySQL主从
1.两台服务器均安装mysql-server和mysql:
# yum -y install mysql-server mysql
2.主从服务器均开启二进制日志,并区分server-id:
①主服务器修改/etc/my.cnf开启二进制日志,设置server-id:
# vim /etc/my.cnf
log-bin=mysql-bin #在mysqld标签下添加下列信息
server-id=10 #id号可随意写数字,主从服务器区分开即可
②从服务器修改/etc/my.cnf开启二进制日志,设置server-id:(区分于主服务器)
# vim /etc/my.cnf
log-bin=mysql-bin #在mysqld标签下添加下列信息
server-id=20 #id号可随意写数字,主从服务器区分开即可
3.两台服务器均开启mysqld服务:
# service mysqld start
4.主服务器授权并查看同步文件:
①主服务器授权:
# mysql>grant replication slave on *.* to slave@'192.168.88.20' identified by '123456';
②)主服务器查看同步二进制日志及大小标签:
# mysql>show master status;
如下图所示:
5.从服务器接受授权:(主动保存授权信息)
①从服务器接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.88.10', #主服务器IP
->master_log_file='mysql-bin.000003', #需要同步的二进制日志
->master_log_pos=259; #以二进制日志大小标签某值为节点开始同步
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
②从服务器验证接受授权是否成功:
# cd /var/lib/mysql
# ls -l
如下图所示:
6.从服务器登录mysql启动从服务器进程:
# mysql -u用户 -p密码
#登录数据库
# mysql>start slave;
#启动从服务器进程
# mysql>show slave status\G;
#查看从服务器内容
如下图所示:
二、MySQL主主
1.两台服务器均安装mysql-server和mysql:
# yum -y install mysql-server mysql
2.两台主主服务器均修改/etc/my.cnf:
①第一台主服务器修改/etc/my.cnf:
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
▲在mysqld标签下添加下列信息:
log-bin=mysql-bin #开启二进制日志
server-id=10 #id号可随意写数字,主从服务器区分开即可
replicate-do-db=test #不推荐设置,默认用test库做同步
binlog-ignore-db=mysql #为提高效率,这两个库的修改信息不记录在日志中
binlog-ignore-db=information_schema
auto-increment-increment=2 #防止主键冲突,数据的id每次自增2
auto-increment-offset=0 #防止主键冲突,此服务器写入数据的id从0开始
②第二台主服务器修改/etc/my.cnf:
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
▲在mysqld标签下添加下列信息:
log-bin=mysql-bin #开启二进制日志
server-id=20 #id号可随意写数字,主从服务器区分开即可
replicate-do-db=test #不推荐设置,默认只同步test库内的变化
binlog-ignore-db=mysql #为提高效率,这两个库的修改信息不记录在日志中
binlog-ignore-db=information_schema
auto-increment-increment=2 #防止主键冲突,数据的id每次自增2
auto-increment-offset=1 #防止主键冲突,此服务器写入数据的id从1开始
3.两台服务器均开启mysqld服务:
# service mysqld start
4.第一台主服务器向第二台主服务器授权并查看同步文件:
①第一台主服务器授权:
# mysql>grant replication slave on *.* to slave@'192.168.88.20' identified by '123456';
②第一台主服务器查看同步二进制日志及大小标签:
# mysql>show master status;
#查看同步二进制日志及目前大小标签
如下图所示:
5.第二台主服务器接收授权:(主动保存授权信息)
①第二台主服务器接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.88.10', #第一台服务器IP
->master_log_file='mysql-bin.000003', #需要同步的二进制日志
->master_log_pos=259; #以二进制日志大小标签某值为节点开始同步
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
②第二台主服务器验证接受授权是否成功:
# cd /var/lib/mysql
# ls -l
如下图所示:
6.第二台主服务器向第一台主服务器授权并查看同步文件:
①第二台主服务器授权:
# mysql>grant replication slave on *.* to slave@'192.168.88.10' identified by '123456';
②第二台主服务器查看同步二进制日志及大小标签:
# mysql>show master status;
#查看同步二进制日志及目前大小标签
如下图所示:
7.第一台主服务器接收授权:(主动保存授权信息)
①第一台主服务器接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.88.20', #第二台服务器IP
->master_log_file='mysql-bin.000003', #需要同步的二进制日志
->master_log_pos=259; #以二进制日志大小标签某值为节点开始同步
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
②第一台主服务器验证接受授权是否成功:
# cd /var/lib/mysql
# ls -l
如下图所示:
8.两台服务器均登录mysql启动从服务器进程:
# mysql -u用户 -p密码
#登录数据库
# mysql>start slave;
#启动从服务器进程
# mysql>show slave status\G;
#查看从服务器内容
如下图所示:
三、MySQL一主多从
1.三台服务器均安装mysql-server和mysql:
# yum -y install mysql-server mysql
2.三台主从服务器均开启二进制日志,并区分server-id:
①主服务器修改/etc/my.cnf开启二进制日志,设置server-id:
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
log-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=10 #id号可随意写数字,三台服务器区分开即可
②第一台从服务器修改/etc/my.cnf开启二进制日志,设置server-id:(区分于其他两台服务器)
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
log-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=20 #id号可随意写数字,三台服务器区分开即可
③第二台从服务器修改/etc/my.cnf开启二进制日志,设置server-id:(区分于其他两台服务器)
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
log-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=30 #id号可随意写数字,三台服务器区分开即可
3.三台服务器均开启mysqld服务:
# service mysqld start
4.主服务器授权并查看同步文件:
①主服务器授权:
# mysql>grant replication slave on *.* to slave@'%' identified by '123456';
②主服务器查看同步二进制日志及大小标签:
# mysql>show master status;
如下图所示:
5.两台从服务器接受授权:(主动保存授权信息)
①两台从服务器均接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.88.10', #主服务器IP
->master_log_file='mysql-bin.000003', #需要同步的二进制日志
->master_log_pos=259; #以二进制日志大小标签某值为节点开始同步
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
②两台从服务器均验证接受授权是否成功:
# cd /var/lib/mysql
# ls -l
如下图所示:
6.两台从服务器均登录mysql启动从服务器进程:
# mysql -u用户 -p密码
#登录数据库
# mysql>start slave;
#启动从服务器进程
# mysql>show slave status\G;
#查看从服务器内容
如下图所示:
四、MySQL多主一从
1.三台服务器均安装mysql-server和mysql:
# yum -y install mysql-server mysql
2.两台服务器开启二进制日志,并区分server-id:
①第一台主服务器修改/etc/my.cnf开启二进制日志,设置server-id:
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
log-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=10 #id号可随意写数字,三台服务器区分开即可
②第二台主服务器修改/etc/my.cnf开启二进制日志,设置server-id:(区分于其他两台服务器)
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
log-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=20 #id号可随意写数字,三台服务器区分开即可
3.两台主服务器均开启mysqld服务:
# service mysqld start
4.两台主服务器均授权并查看同步文件:
①两台主服务器均授权:
# mysql>grant replication slave on *.* to slave@'192.168.88.30' identified by '123456';
②主服务器查看同步二进制日志及大小标签:
# mysql>show master status;
如下图所示:
5.从服务器修改/etc/my.cnf:
# vim /etc/my.cnf
▲添加以下信息,在尾部添加即可,与其它标签持平:
[mysqld_multi] #多进程模式,以此标签以下配置启动mysql
mysqld=/usr/bin/mysqld_safe #每个单一进程的启动仍依靠mysqld_safe
mysqladmin=/usr/bin/mysqladmin #修改用户名、密码依靠文件所在位置
log=/var/log/multi.log #日志文件单独记录在此文件中
[mysqld10] #10标签
port=3306 #端口
datadir=/var/lib/mysqla/ #数据库文件保存位置
pid-file=/var/lib/mysqla/mysqld.pid #pid文件所在位置
socket=/var/lib/mysqla/mysql.sock #sock文件所在位置
user=mysql #运行时所使用的默认用户
server-id=30 #两个标签server-id相同
[mysqld20] #20标签
port=3307 #端口
datadir=/var/lib/mysqlb/ #数据库文件保存位置
pid-file=/var/lib/mysqlb/mysqld.pid #pid文件所在位置
socket=/var/lib/mysqlb/mysql.sock #sock文件所在位置
user=mysql #运行时所使用的默认用户
server-id=30 #两个标签server-id相同
6.从服务器初始化数据库,生成mysqla和mysqlb目录:
①初始化,生成两个数据库文件保存目录:
# mysql_install_db --datadir=/var/lib/mysqla --user=mysql
#初始化并生成mysqla目录
# mysql_install_db --datadir=/var/lib/mysqlb --user=mysql
#初始化并生成mysqlb目录
#--datadir=:指定数据库文件保存位置
#--user=:指定以mysql用户身份运行
▲有两个ok代表初始化成功
②检查两个目录中是否有数据:
# cd /var/lib/mysqla
# ls -l
# cd /var/lib/mysqlb
# ls -l
如下图所示:
7.从服务器设置mysqla,mysqlb目录及以下文件的属主为mysql:(防止出现权限问题)
# chown -R mysql.mysql /var/lib/mysqla
# chown -R mysql.mysql /var/lib/mysqlb
#修改两个目录及以下子文件的属主为mysql
8.启动从服务器线程:
# mysqld_multi --defaults-file=/etc/my.cnf start 10
# mysqld_multi --defaults-file=/etc/my.cnf start 20
▲注意:此处的号码和my.cnf文件中两个[mysqld10]、[mysqld20]标签号码要一致
9.从服务器登录mysql:(用两个窗口登录)
①从服务器登录3306端口:
# mysql -P 3306 -S /var/lib/mysqla/mysql.sock
②从服务器登录3307端口:
# mysql -P 3307 -S /var/lib/mysqlb/mysql.sock
10.从服务器两个线程均接受授权:
①3306端口的线程接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.88.10', #第一台主服务器IP
->master_log_file='mysql-bin.000003', #需要同步的第一台主服务器的二进制日志
->master_log_pos=259; #第一台主服务器的二进制日志的大小标签节点
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
②3307端口的线程接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.88.20', #第二台主服务器IP
->master_log_file='mysql-bin.000003', #需要同步的第二台主服务器的二进制日志
->master_log_pos=259; #第二台主服务器的二进制日志的大小标签节点
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
11.从服务器两个线程均启动从服务进程:
# mysql>start slave;
#启动从服务器进程
# mysql>show slave status\G;
#查看从服务器内容
如下图所示:
四、MySQL读写分离
1.先搭建一个主从关系的服务器:
①主从服务器均安装mysql mysql-server:
# um -y install mysql-server mysql
②主从服务器均开启二进制日志,并区分server-id:
⑴主服务器修改/etc/my.cnf开启二进制日志,设置server-id:
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
og-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=20 #id号可随意写数字,主从服务器区分开即可
⑵从服务器修改/etc/my.cnf开启二进制日志,设置server-id:(区分于主服务器)
# vim /etc/my.cnf
#在主服务器主配置文件/etc/my.cnf中添加下列信息:
og-bin=mysql-bin ▲在mysqld标签下添加下列信息
server-id=30 #id号可随意写数字,主从服务器区分开即可
③两台服务器均开启mysqld服务:
# service mysqld start
④主服务器授权并查看同步文件:
⑴主服务器授权:
# mysql>grant replication slave on *.* to slave@'192.168.126.100' identified by '123456';
⑵主服务器查看同步二进制日志及大小标签:
# mysql>show master status;
⑤从服务器接受授权:(主动保存授权信息)
⑴从服务器接受授权:
# mysql>change master to
->master_user='slave', #被授权用户
->master_password='123456', #被授权用户的密码
->master_host='192.168.126.50', #主服务器IP
->master_log_file='mysql-bin.000003', #需要同步的二进制日志
->master_log_pos=259; #以二进制日志大小标签某值为节点开始同步
▲注意:此时选定的大小标签代表从服务器以此为节点开始同步;若从头为节点,则将原先的动作先执行一遍再同步
⑵从服务器验证接受授权是否成功:
# cd /var/lib/mysql
# ls -l
如下图所示:
⑥从服务器登录mysql启动从服务器进程:
# mysql -u用户 -p密码
#登录数据库
# mysql>start slave;
#启动从服务器进程
# mysql>show slave status\G;
#查看从服务器内容
如下图所示:
⑦主从服务器均授权amoeba服务器可以连接数据库:
⑴主服务器授权amoeba服务器可以连接数据库:
# mysql>grant all on test.* to user1@'192.168.126.18' identified by '123456';
⑵从服务器授权amoeba服务器可以连接数据库:
# mysql>grant all on test.* to user1@'192.168.126.18' identified by '123456';
2.中间件服务器配置读写分离:
①安装gcc环境:(amoeba需要源码安装)
# yum -y install gcc*
②上传、解压缩软件包:(amoeba是由java语言编写的,所以先安装jdk)
# yum -y install lrzsz
#安装lrzsz命令软件包
# unzip amoeba-n.zip
#解压缩
# cd amoeba-n
# unzip amoeba-mysql-1.3.1-BETA.zip -d /usr/local/amoeba
#解压缩amoeba至/usr/local/amoeba目录
# tar -xf jdk-7u40-linux-x64.gz
# mv jdk1.7.0_40/ /usr/local/jdk
#解压缩jdk,移动解压缩后的目录至/usr/local/下并改名为jdk
③修改jdk目录所属:
# cd /usr/local
# chown -R root.root jdk/
#修改jdk目录所有者、所属组为root
④修改环境变量文件(/etc/profile),声明用java写出来的程序如何调用:
⑴修改/etc/profile:
# vim /etc/profile
▲在文件结尾处添加下列信息即可:
export JAVA_HOME=/usr/local/jdk
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/bin/tools.jar:$JAVA_HOME/lib/dt.jar
⑵加载环境变量文件:(/etc/profile)
# source /etc/profile
⑶测试:
# java -version
#测试,显示版本号代表环境变量可用
如下图所示:
⑤配置amoeba:(已解压至/usr/local/amoeba目录)
⑴修改amoeba目录所属:
# cd /usr/local
# chown -R root.root amoeba/
#修改amoeba目录所有者、所属组为root
⑵给amoeba程序下的命令添加x权限:(/usr/local/amoeba/bin)
# chmod -R +x /usr/local/amoeba/bin/
#为了让amoeba程序下的命令能正常执行
⑶修改amoeba主配置文件:(/usr/local/amoeba/conf/amoeba.xml)
# cp -a /usr/local/amoeba/conf/amoeba.xml /usr/local/amoeba/conf/amoeba.xml.back
#备份一份,防止配置文件修改错误
# vim /usr/local/amoeba/conf/amoeba.xml
配置文件中所需要修改的参数如下图所示:
若有多台数据库服务器则需要多个标签:
主数据库服务器对应标签:
从数据库服务器对应标签:
▲注意:若有多个功能池则需要多个标签:
写功能池对应标签(写数据时向哪个数据库内写入):
读功能池对应标签(读取数据时访问哪个数据库):
▲注意:建议默认访问的池写主数据库,server1或writepool皆可,因为主数据库能做更多操作:
⑥修改amoeba启动脚本:(/usr/local/amoeba/bin/amoeba)
# vim /usr/local/amoeba/bin/amoeba
▲修改以下参数:
DEFAULT_OPTS="-server -Xms256m -Xmx256m -Xss256k" #修改为256即可
⑦测试主从服务器的授权是否成功:(amoeba服务器能否手动连接两台数据库)
⑴amoeba服务器下载mysql客户端软件:
# yum -y install mysql
#下载数据库客户端软件
⑵手动连接数据库:
# mysql -h192.168.126.50 -uuser1 -p123456
# mysql -h192.168.126.100 -uuser1 -p123456
⑧启动amoeba服务:
⑴amoeba服务器下载mysql客户端软件:
# nohup bash -x /usr/local/amoeba/bin/amoeba &
▲输完命令按回车即可,若不显示Done状态这代表启动成功
如下图所示:
⑵检查程序是否运行成功:
# ps aux | grep amoeba
#若过滤到代表程序运行成功
# netstat -antp | grep ":8066"
#若过滤到代表程序运行成功