晋江住房和城乡建设局网站,做彩票网站能挣到钱吗,深圳品牌火锅店加盟,有域名在本机上做网站一主一从
准备两台服务器(以53为主,54为从)启动主服务器binlog日志 vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id53 //指定主服务器的server-id为53
log-binmysql53 //修改binlog日志命名
:wqsystemctl restart mysqld //重载mysqld服务,使得以上配置生效 创…一主一从
准备两台服务器(以53为主,54为从)启动主服务器binlog日志 vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id53 //指定主服务器的server-id为53
log-binmysql53 //修改binlog日志命名
:wqsystemctl restart mysqld //重载mysqld服务,使得以上配置生效 创建复制/同步用户并授权 [rootmysql53 ~]# mysqlmysql create user repluser% identified by 123qqq...A;Query OK, 0 rows affected (0.11 sec)mysql grant replication slave on *.* to repluser%;Query OK, 0 rows affected (0.09 sec) 修改从服务器的server-id为54,方便后续主从同步 [rootmysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id54
:wq
[rootmysql54 ~]# systemctl restart mysqld 为从服务器指定主服务器的信息 [rootmysql54 ~]# mysql
mysql change master to master_host192.168.88.53 , master_userrepluser , master_password123qqq...A ,master_log_filemysql53.000001 , master_log_pos667;
// 其中的master_log_file和master_log_pos记录保持主服务器binlog日志记录
Query OK, 0 rows affected, 8 warnings (0.34 sec)
mysql start slave ; //启动slave进程
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql show slave status \G //查看状态信息
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.88.53 //主服务器Master_User: repluser //指定的数据同步用户Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql53.000001 //主服务器binlog信息Read_Master_Log_Pos: 667Relay_Log_File: mysql54-relay-bin.000002Relay_Log_Pos: 322Relay_Master_Log_File: mysql53.000001Slave_IO_Running: Yes //IO线程Slave_SQL_Running: Yes //SQL线程 测试 给主服务器添加测试用户(plj)并授予权限 [rootmysql53 ~]# mysqlmysql create user plj% identified by 123456;Query OK, 0 rows affected (0.14 sec)mysql grant all on gamedb.* to plj% ;Query OK, 0 rows affected (0.12 sec)mysql create database gamedb;
Query OK, 1 row affected (0.24 sec)
mysql create table gamedb.user(name char(10) , class char(3));
Query OK, 0 rows affected (1.71 sec)
mysql insert into gamedb.user values (yaya,nsd);
Query OK, 1 row affected (0.14 sec)
mysql select * from gamedb.user;
-------------
| name | class |
-------------
| yaya | nsd |
-------------
1 row in set (0.01 sec)
mysql 从服务器查看数据 [rootmysql50 ~]# mysql -h192.168.88.54 -uplj -p123456 –e ‘select * from gamedb.user’-------------| name | class |-------------| yaya | nsd |-------------[rootmysql54 ~]#
一主多从
创建思路同上类似,需要对新的从服务器指定主服务器的信息
主从从
创建思路同上类似,需要对新的从从服务器指定从服务器的信息
读写分离
原理
利用mycat中间件软件提供读写分离功能,一主一从结构实现数据同步/复制,客户端登录mycat服务,访问对应数据库,发送sql请求,交给主master服务器处理写入,收到sql读请求时,交给slave服务器读数据.
步骤
搭建一主一从结构(实现主从之间的数据复制/同步) 如:56主57从58为mycat服务器对58部署mycat服务并配置 mycat软件包 //安装jdk[rootmycat58 upload]# yum -y install java-1.8.0-openjdk.x86_64//安装解压命令[rootmycat58 upload]# which unzip || yum -y install unzip//安装mycat[rootmycat58 upload]# unzip mycat2-install-template-1.21.zip[rootmycat58 upload]# mv mycat /usr/local///安装依赖[rootmycat58 upload]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib///修改权限[rootmycat58 upload]# chmod -R 777 /usr/local/mycat/ //定义连接mycat服务使用的用户以及密码[rootmycat58 ~]# vim /usr/local/mycat/conf/users/root.user.json{dialect:mysql,ip:null,password:654321, 密码transactionType:proxy,username:mycat 用户名}:wq//定义连接的数据库服务器[rootmycat58 ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.data{dbType:mysql,idleTimeout:60000,initSqls:[],initSqlsGetConnection:true,instanceType:READ_WRITE,maxCon:1000,maxConnectTimeout:3000,maxRetryCount:5,minCon:1,name:prototypeDs,password:123456, 密码type:JDBC,url:jdbc:mysql://localhost:3306/mysql?useUnicodetrueserverTimezoneAsia/ShanghaicharacterEncodingUTF-8, 连接本机的数据库服务user:plj, 用户名weight:0}:wq//在mycat服务器上运行数据库服务
[rootmycat58 ~]# yum -y install mysql-server mysql 安装软件
[rootmycat58 ~]# systemctl start mysqld 启动服务
[rootmycat58 ~]# mysql 连接服务
mysql create user plj% identified by 123456; 创建plj用户
Query OK, 0 rows affected (0.05 sec)
mysql grant all on *.* to plj% ; 授予权限
Query OK, 0 rows affected (0.39 sec)
mysql exit 断开连接
Bye//启动mycat服务[rootmycat58 ~]# /usr/local/mycat/bin/mycat helpUsage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }[rootmycat58 ~]# /usr/local/mycat/bin/mycat startStarting mycat2...//半分钟左右 能看到端口[rootmycat58 ~]# netstat -utnlp | grep 8066tcp6 0 0 :::8066 :::* LISTEN 57015/java //连接mycat服务
[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
--------------------
3 rows in set (0.11 sec)//在mysql服务器上配置读写分离
//添加数据源
MySQL /* mycat:createdatasource{
name:whost56, url:jdbc:mysql://192.168.88.56:3306,user:plja,password:123456}*/;
Query OK, 0 rows affected (0.25 sec)
//添加mysql57数据库服务器
Mysql/* mycat:createdatasource{
name:rhost57, url:jdbc:mysql://192.168.88.57:3306,user:plja,password:123456}*/;
//查看数据源
mysql /*mycat:showDataSources{}*/ \G//添加的数据源以文件的形式保存在安装目录下
[rootmycat58 conf]# ls /usr/local/mycat/conf/datasources/
prototypeDs.datasource.json rhost57.datasource.json whost56.datasource.json//在主服务器添加plja用户
[rootmysql56 ~]# mysql
mysql create user plja% identified by 123456;
Query OK, 0 rows affected (0.06 sec)
mysql grant all on *.* to plja%;
Query OK, 0 rows affected (0.03 sec)
mysqlexit
[rootmysql56 ~]#
//在slave服务器查看是否同步成功
[rootmysql57 ~]# mysql -e select user , host from mysql.user where userplja
------------
| user | host |
------------
| plja | % |
------------//创建集群
[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
//创建集群
mysql/*!mycat:createcluster{
name:rwcluster,
masters:[whost56],
replicas:[rhost57]
}*/ ;
Mysql
//查看集群信息
mysql /* mycat:showClusters{}*/ \G//创建的集群以文件的形式保存在目录下
[rootmycat58 conf]# ls /usr/local/mycat/conf/clusters/
prototype.cluster.json rwcluster.cluster.json//修改master角色主机仅负责写访问
[rootmycat58 ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
{instanceType:WRITE, 仅负责写访问}//修改slave角色主机仅负责读访问
[rootmycat58 ~]# vim /usr/local/mycat/conf/datasources/rhost57.datasource.json
{
instanceType:READ,仅负责读访问
}//修改读策略
[rootmycat58 ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
{ readBalanceType:BALANCE_ALL_READ,} //获取集群中允许读的数据源//重启mycat服务
[rootmycat58 ~]# /usr/local/mycat/bin/mycat restart 测试 //连接mycat服务,创建testdb测试数据库,并指定该数据库使用的集群
[rootmycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql create database testdb;
Query OK, 0 rows affected (0.30 sec)
mysql exit
Bye
//指定testdb库存储数据使用的集群
[rootmycat58 ~]# vim /usr/local/mycat/conf/schemas/testdb.schema.json
{customTables:{},globalTables:{},normalProcedures:{},normalTables:{},schemaName:testdb,targetName:rwcluster, 添加此行,之前创建的集群名rwclustershardingTables:{},views:{}
}
:wq
//重启mycat,使得上配置生效
[rootmycat58 ~]# /usr/local/mycat/bin/mycat restart//连接mycat数据库服务,并在testdb插入数据,可见主从都有插入的数据
//另外连接从服务器并插入数据,而主服务器没有该数据,mycat可以查看到该数据.
