mysql架构精选

落日映苍穹つ 2022-04-24 05:16 145阅读 0赞

# [mysql架构精选][mysql] #

◆主从架构  
1.安装服务（主从）  
yum -y install mysql\*  
/etc/init.d/mysqld start  
2.修改配置文件:/etc/my.conf(主从)  
vi /etc/my.conf  
log-bin=mysql-bin \#\#\#开启二进制日志文件  
server-id=131 \#\#\#声明服务器ID，不重复即可  
/etc/init.d/mysqld restart  
3.1进入主—数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#创建从服务器登录用户  
\#\#grant replication slave on \*.\* to '用户'@'%' identified by '密码';  
grant replication slave on \*.\* to 'master'@'%' identified by '123';  
show master status; \#\#查看二进制文件名，以及大小  
3.2进入从-数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#确定从主服务器同步  
\#\#change master to master\_host='主服务器IP',master\_user='用户',master\_password='密码',master\_log\_file='二进制文件',master\_log\_pos=文件空间大小;  
change master to master\_host='192.168.116.131',master\_user='master',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=385;  
\#\#启动同步  
start slave;  
\#\#查看同步服务开启状态  
show slave status\\G;  
4.测试  
主：create database aa;  
从：show databases;  
◆主从从架构  
1.安装服务（主从）  
yum -y install mysql\*  
/etc/init.d/mysqld start  
2.修改配置文件:/etc/my.conf(主从)  
vi /etc/my.conf  
log-bin=mysql-bin \#\#\#开启二进制日志文件  
server-id=131 \#\#\#声明服务器ID，不重复即可  
/etc/init.d/mysqld restart  
3.1进入主—数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#创建从服务器登录用户  
\#\#grant replication slave on \*.\* to '用户'@'%' identified by '密码';  
grant replication slave on \*.\* to 'master'@'%' identified by '123';  
show master status; \#\#查看二进制文件名，以及大小  
3.2进入从1-数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#确定从主服务器同步  
\#\#change master to master\_host='主服务器IP',master\_user='用户',master\_password='密码',master\_log\_file='二进制文件',master\_log\_pos=文件空间大小;  
change master to master\_host='192.168.116.131',master\_user='master',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=385;  
\#\#启动同步  
start slave;  
\#\#查看同步服务开启状态  
show slave status\\G;  
3.3进入从2-数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#确定从主服务器同步  
\#\#change master to master\_host='主服务器IP',master\_user='用户',master\_password='密码',master\_log\_file='二进制文件',master\_log\_pos=文件空间大小;  
change master to master\_host='192.168.116.131',master\_user='master',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=385;  
\#\#启动同步  
start slave;  
\#\#查看同步服务开启状态  
show slave status\\G;  
4.测试  
主：create database bb;  
从1:show databases;  
从2:show databases;  
◆主主架构（互为主从）  
\#\#\#服务器有数据的情况，需要手动先同步一致，cp重定向到mysql  
1.安装服务（主主）  
yum -y install mysql\*  
/etc/init.d/mysqld start  
2.修改配置文件:/etc/my.conf(主主)  
vi /etc/my.conf  
log-bin=mysql-bin \#\#\#开启二进制日志文件  
server-id=131 \#\#\#声明服务器ID，不重复即可  
\#\#\#下面几个看实际情况使用，实验环境可忽略  
replicate-do-db=test \#\#\#需要同步的数据库  
binlog-ignore-db=mysql \#\#\#不需要同步的数据库  
binlog-ignore-db=information\_schema \#\#不需要同步的数据库  
auto-increment-increment=2 \#\#\#为了保证含有自增长的不会冲突  
auto-increment-offset=1 \#\#\#为了保证含有自增长的不会冲突  
log-slave-updates=true  
/etc/init.d/mysqld restart  
3.1进入主1—数据库 ip:192.168.116.131  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#创建从服务器登录用户  
\#\#grant replication slave on \*.\* to '用户'@'%' identified by '密码';  
grant replication slave on \*.\* to 'master'@'%' identified by '123';  
flush privileges;  
show master status; \#\#查看二进制文件名，以及大小  
\#\#确定从主服务器同步  
\#\#change master to master\_host='主服务器IP',master\_user='用户',master\_password='密码',master\_log\_file='二进制文件',master\_log\_pos=文件空间大小;  
change master to master\_host='192.168.116.132',master\_user='slave',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=384;  
\#\#启动同步  
start slave;  
\#\#查看同步服务开启状态  
show slave status\\G;  
3.2进入主2-数据库 ip:192.168.116.132  
mysqladmin -uroot password '123'  
mysql -uroot –p  
\#\#创建从服务器登录用户  
\#\#grant replication slave on \*.\* to '用户'@'%' identified by '密码';  
grant replication slave on \*.\* to 'slave'@'%' identified by '123';  
show master status; \#\#查看二进制文件名，以及大小  
\#\#确定从主服务器同步  
\#\#change master to master\_host='主服务器IP',master\_user='用户',master\_password='密码',master\_log\_file='二进制文件',master\_log\_pos=文件空间大小;  
change master to master\_host='192.168.116.131',master\_user='master',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=247;  
\#\#启动同步  
start slave;  
\#\#查看同步服务开启状态  
show slave status\\G;  
4.测试  
主1：create database aa;show databases;主2:show databases;  
主2：create database aa;show databases;主1:show databases;  
◆主主从架构  
1.安装服务（主主从）  
yum -y install mysql\*  
/etc/init.d/mysqld start  
2.主配置文件:/etc/my.conf  
2.1两台主服务器  
vi /etc/my.conf  
log-bin=mysql-bin \#\#\#开启二进制日志文件  
server-id=131和132 \#\#\#声明服务器ID，不重复即可  
/etc/init.d/mysqld restart  
2.2从服务器  
vi /etc/my.cnf  
\[mysqld\_multi\]  
mysqld=/usr/bin/mysqld\_safe  
mysqladmin=/usr/bin/mysqladmin  
log=/tmp/multi.log  
\[mysqld131\]  
port=3306  
pid-file=/var/lib/mysqla/mysqld.pid  
datadir=/var/lib/mysqla  
socket=/var/lib/mysqla/mysql.sock  
server-id=129  
user=mysql  
\[mysqld132\]  
port=3307  
pid-file=/var/lib/mysqlb/mysqld.pid  
datadir=/var/lib/mysqlb  
socket=/var/lib/mysqlb/mysql.sock  
server-id=129  
user=mysql  
3.1 主1进入数据库  
mysqladmin -uroot password '123'  
mysql -uroot -p  
grant replication slave on \*.\* to 'master1'@'%' identified by '123';  
show master status;  
3.2 主2进入数据库  
mysqladmin -uroot password '123'  
mysql -uroot -p  
grant replication slave on \*.\* to 'master2'@'%' identified by '123';  
show master status;  
3.3 从服务器  
mysql\_install\_db --datadir=/var/lib/mysqla --user=mysql \#\#\#初始化mysqla数据库  
mysql\_install\_db --datadir=/var/lib/mysqlb --user=mysql \#\#\#初始化mysqlb数据库  
cd /var/lib  
chown -R mysql /var/lib/mysqla/ \#\#修改文件权限  
chown -R mysql /var/lib/mysqlb/ \#\#修改文件权限  
mysqld\_multi --defaults-file=/etc/my.cnf start 131 \#\#主1id号，开起数据库01，为主1备份  
mysqld\_multi --defaults-file=/etc/my.cnf start 132 \#\#主2id号，开起数据库02，为主2备份  
测试端口：netstat -an |grep 3306&3307  
mysqladmin -u root password '123' -S /var/lib/mysqla/mysql.sock \#\#建立131数据库密码  
mysqladmin -u root password '123' -S /var/lib/mysqlb/mysql.sock \#\#建立132数据库密码

\#\#\#\#进入mysqla ----131数据库配置  
mysql -uroot -p -S /var/lib/mysqla/mysql.sock  
change master to master\_host='192.168.116.131',master\_user='master1',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=247;  
start slave;  
show slave status\\G;  
exit

\#\#\#\#进入mysqlb -----132数据库配置  
mysql -uroot -p -S /var/lib/mysqlb/mysql.sock  
change master to master\_host='192.168.116.132',master\_user='master2',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=247;  
start slave;  
show slave status\\G;  
exit  
4.测试  
主1备份测试：  
主1: create database aa;  
从:  
mysql -uroot -p -S /var/lib/mysqla/mysql.sock  
show databases;  
主2备份测试:  
主2:create database bb;  
从：  
mysql -uroot -p -S /var/lib/mysqlb/mysql.sock  
show databases;

◆mysql读写分离 -----amoeba  
1.两台数据库主从关系  
yum -y install mysql\*  
/etc/init.d/mysqld start  
vi /etc/my.conf  
log-bin=mysql-bin \#\#\#开启二进制日志文件  
server-id=131 \#\#\#声明服务器ID，不重复即可  
/etc/init.d/mysqld restart  
\#\#主数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
grant all privileges on \*.\* to 'amoeba'@'%' identified by '123';  
show master status; \#\#查看二进制文件名，以及大小

\#\#\#从数据库  
mysqladmin -uroot password '123'  
mysql -uroot –p  
change master to master\_host='192.168.116.131',master\_user='amoeba',master\_password='123',master\_log\_file='mysql-bin.000001',master\_log\_pos=382;  
grant all privileges on \*.\* to 'amoeba'@'%' identified by '123';

\#\#查看同步服务开启状态  
show slave status\\G;  
\#启动同步  
start slave;  
\#\#\#测试  
主：create database aa;  
从：show databases;

2.配置amobe服务器  
yum -y install lrzsz mysql  
rz amoeba-n.iso  
mkdir /iso  
mount -o loop amoeba-n.iso /iso/  
cp /iso/\* .  
tar zxvf jdk-7u40-linux-x64.gz  
mv jdk1.7.0\_40/ /usr/local/jdk  
vi /etc/profile  
\#JDK  
JAVA\_HOME=//usr/local/jdk  
export JAVA\_HOME  
PATH=$JAVA\_HOME/bin:$PATH  
export PATH CLASSPATH=.:$JAVA\_HOME/lib/tools.jar:$JAVA\_HOME/lib/dt.jar:$CLASSPATH export CLASSPATH  
source /etc/profile

\#\#\#测试：echo $PATH java -version

unzip amoeba-mysql-1.3.1-BETA.zip -d /usr/local/amoeba  
chmod -R a+x /usr/local/amoeba/bin/  
vi /usr/local/amoeba/conf/amoeba.xml  
\#\#\#\#\#amoeba主配置信息  
<?xml version="1.0" encoding="gbk"?>  
<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">  
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

<property name="ipAddress">192.168.116.129</property>  
  
  
<property name="clientSideThreadPoolSize">30</property>

<property name="serverSideThreadPoolSize">30</property>

<property name="tcpNoDelay">true</property>

<property name="user">root</property>

<property name="password">456</property>

<property name="queryTimeout">60</property>  
</server>

<connectionManagerList>

</connectionManager>  
</connectionManagerList>

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">192.168.116.131</property>  
<property name="schema">aa,bb</property>

<property name="user">amoeba</property>

</factoryConfig>

<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">  
<property name="maxActive">200</property>  
<property name="maxIdle">200</property>  
<property name="minIdle">10</property>  
<property name="minEvictableIdleTimeMillis">600000</property>  
<property name="timeBetweenEvictionRunsMillis">600000</property>  
<property name="testOnBorrow">true</property>  
<property name="testWhileIdle">true</property>  
</poolConfig>  
</dbServer>

<property name="poolNames">server1</property>  
</poolConfig>  
</dbServer>  
<dbServer name="server2">

<property name="manager">defaultManager</property>

<property name="port">3306</property>

<property name="ipAddress">192.168.116.132</property>  
<property name="schema">aa,bb</property>

<property name="user">amoeba</property>

</factoryConfig>

<property name="poolNames">server2</property>  
</poolConfig>  
</dbServer>  
</dbServerList>

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">  
<property name="ruleConfig">$\{amoeba.home\}/conf/rule.xml</property>  
<property name="functionConfig">$\{amoeba.home\}/conf/functionMap.xml</property>  
<property name="ruleFunctionConfig">$\{amoeba.home\}/conf/ruleFunctionMap.xml</property>  
<property name="LRUMapSize">1500</property>  
<property name="defaultPool">master</property>

<property name="writePool">master</property>  
<property name="readPool">slave</property>  
<property name="needParse">true</property>  
</queryRouter>  
</amoeba:configuration>  
\#\#\#\#\#\#amoeba主配置信息

vi /usr/local/amoeba/bin/amoeba  
DEFAULT\_OPTS="-server -Xms256m -Xmx256m -Xss256k" \#\#\#128k改成256k

\#\#\#启动amoeba  
nohup bash -x /usr/local/amoeba/bin/amoeba &  
\#\#测试服务是否启动  
netstat -an | grep 9000

yum -y install mysql  
mysql -uroot -p -h 192.168.116.129 -P 9000 \#\#本机amoeba密码：456

\#\#\#测试amoeba效果，关闭从，测试主是否可读可写

posted @ 2018-05-24 15:41 [疯狂的米粒儿][Link 1] 阅读( ...) 评论( ...) [编辑][Link 2] 收藏

[mysql]: https://www.cnblogs.com/Su-per-man/p/9083223.html
[Link 1]: https://www.cnblogs.com/Su-per-man/
[Link 2]: https://i.cnblogs.com/EditPosts.aspx?postid=9083223