CNDBA社区
Keepalived + 双mycat + mysql主主复制
高可用环境搭建
1.1 环境规划
Mysql主主复制 + Mycat
主机名 软件版本 IP 操作系统 下载地址
dbmaster1 5.6.21 172.41.176.115 Centos7
dbmaster2 5.6.21 172.41.176.116 Centos7
mycat-1 1.6.5 172.41.176.124 Centos7 http: //mycat.io/
mycat-2 1.6.5 172.41.176.125 Centos7
Virtual IP 172.41.176.119
1.2 Mysql安装步骤
在Centos7中用MariaDB代替了mysql数据库。所以在新安装MySQL前必须做好对系统的清理工作
1.2.1 清理maridb数据库
[root@dbmaster1 ~]# rpm -qa | grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
mariadb-embedded-5.5.56-2.el7.x86_64
mariadb-devel-5.5.56-2.el7.x86_64
mariadb-bench-5.5.56-2.el7.x86_64
mariadb-embedded-devel-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
mariadb-test-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-server-5.5.56-2.el7.x86_64
warning: /var/log/mariadb/mariadb.log savedas /var/log/mariadb/mariadb.log.rpmsave
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-embedded-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-devel-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]#
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-bench-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-embedded-devel-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-libs-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]# rpm -e --nodepsmariadb-test-5.5.56-2.el7.x86_64
[root@dbmaster1 ~]# rpm -qa | grep mariadb
查看下是否有系统自带mysql的rpm包，如果有，需要删除自带的旧rpm包
[root@dbmaster1 ~]#rpm -qa | grep mysql
mysql-libs-5.1.71-1.el6.x86_64
[root@dbmaster1 ~]# yum-y remove mysql-libs-5.1*
[root@dbmaster1~]# rpm-qa | grep mysql
[root@dbmaster1 ~]#
1.2.2 通过yum方式安装mysql 5.6
可通过wget(wget命令可自行安装，yum install wget)命令下载到主机上，也可以直接网页下载再上传
这里我们通过wget命令
[root@dbmaster1 ~]# wget http: //dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
RPM安装报错：
[root@dbmaster1 ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
error: Failed dependencies:
/usr/bin/perlis needed by MySQL-server-5.6.21-1.rhel5.x86_64
解决方案：貌似提示注释器错误，没有/usr/bin/perl文件或者档案，解决办法（安装perl跟perl-devel即可）：
执行 yum -y install perlperl-devel
[root@dbmaster1 ~]# rpm -ivhmysql-community-release-el7-5.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-release-el7-5 ################################# [100%]
查看当前可用的mysql安装资源
[root@dbmaster1 ~]# yum repolist enabled | grep"mysql.-community."
mysql-connectors-community/x86_64 MySQL Connectors Community 42
mysql-tools-community/x86_64 MySQL Tools Community 55
mysql56-community/x86_64 MySQL 5.6 Community Server 378
1.2.3 mysql安装
[root@dbmaster1 ~]# yum installmysql-community-server
……. 安装输出过程省略(安装完毕进入下一步)………
1.3 相关配置
1.3.1关闭防火墙以及selinux
[root@dbmaster1 usr]# vi /etc/hosts
172.41.176.115dbmaster1
[root@dbmaster1 cl]# systemctl stop firewalld.service
[root@dbmaster1 cl]# systemctl disable firewalld.service
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink/etc/systemd/system/basic.target.wants/firewalld.service.
[root@dbmaster1 cl]# vi /etc/sysconfig/selinux
[root@mycat-2 ~]# setenforce 0
1.3.2 修改mysql配置文件路径
找到配置文件所在路径
[root@dbmaster1 ~]# find / -name"*.cnf"
/etc/pki/tls/openssl.cnf
/etc/my.cnf
/usr/share/doc/mysql-community-server-5.6.39/my-default.cnf
/usr/share/mysql/my-default.cnf
当前存有/etc/my.cnf文件，是因为centos7自带maridb生成的，可覆盖或者更改名再cp
mysql启动默认是从/etc/my.cnf读取的，所以你别的地方有配置文件的话建议直接移到/etc目录下
[root@dbmaster1 ~]# cp /usr/share/mysql/my-default.cnf/etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
另外my.cnf配置文件可自行新建…..
[root@dbmaster1 ~]# mv /etc/my.cnf/etc/my.cnf.bak
[root@dbmaster1 ~]# vi /etc/my.cnf
1.3.3 配置my.cnf文件
查找mysql.sock套接文件，以确保localhost可直连mysql
[root@dbmaster1 mysql]# find / -name"*.sock"
/var/lib/mysql/mysql.sock
配置my.conf文件
[mysqld]
datadir=/data/mysqldb
basedir=/usr/lib64/mysql/
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
server-id = 2
auto-increment-increment = 2
auto-increment-offset = 2
explicit_defaults_for_timestamp = 1
character_set_server=utf8
interactive_timeout = 57600
log-bin = mysql-bin
expire-logs-days = 100
replicate-do-db = yunqu #需要同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
slave-skip-errors=all
log-slave-updates
symbolic-links=0
skip-name-resolve
default-storage-engine=INNODB
max_connect_errors = 100
table_open_cache = 4096
max_allowed_packet = 128M
binlog_cache_size = 4M
max_heap_table_size = 128M
read_rnd_buffer_size = 16m
sort_buffer_size = 16M
join_buffer_size = 16m
thread_cache_size = 16
thread_concurrency = 4
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
thread_stack = 512K
transaction_isolation =REPEATABLE-READ
tmp_table_size = 128M
general_log=ON
general_log_file=/var/log/mysqld.log
long_query_time = 6
key_buffer_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size =10G
#myisam_max_extra_sort_file_size =10G
myisam_repair_threads = 1
myisam_recover
skip-federated
# * INNODB 相关选项*
innodb_additional_mem_pool_size =64M
innodb_buffer_pool_size = 6G
innodb_data_file_path =ibdata1:10M:autoextend
innodb_read_io_threads= 8
innodb_write_io_threads= 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit =2
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct =90
innodb_lock_wait_timeout = 120
innodb_file_per_table = on
max_allowed_packet = 32M
备注：主机1和主机2都只有server-id不同和 auto-increment- offset不同
auto-increment-offset是用来设定数据库中自动增长的起点的，回为这两能
服务器都设定了一次自动增长值2，所以它们的起点必须得不同，这样才能避
免两台服务器数据同步时出现主键冲突replicate-do-db 指定同步的数据库，
我们只在两台服务器间同步wang数据库
另：auto-increment-increment的值应设为整个结构中服务器的总数，本案例用到两台服务器，所以值设为2
主主复制配置说明
server-id=115 #数据库ID,可指定
log-bin=myslq-bin #启用二进制日志
#binlog-do-db=tudou1 #需要同步的数据库,这里同步tudou1和tudou2两个数据库
binlog-ignore-db=mysql #忽略同步的数据库,其余同步
log-bin=/var/log/mysqld.log #设置生成的log文件名，如果没有var/log/mysqlbin这个目录，则需要创建，并且执行chown -R mysql.mysql/var/log/mysqlbin
log-slave-updates #把从库的写操作记录到binlog中
expire_logs_days=365 #日志文件过期天数，默认是 0，表示不过期
auto-increment-increment=2 #设定为主服务器的数量，防止 auto_increment 字段重复
auto-increment-offset=1 #自增长字段的初始值，在多台 master 环境下，不会出现自增长 ID 重复
binlog_format =MIXED #非必需
relay-log = mysqld-relay-bin#定义中继日志名，开启从服务器中继日志
指定配置文件并安装DB
[root@dbmaster2~]# mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --force
激活启动mysql
[root@dbmaster1 cl]# systemctl start mysqld.service
[root@dbmaster1 ~]# systemctl enable mysqld.service
验证：
[root@dbmaster1 ~]# ps -ef | grep mysql
mysql 2782 1 0 01:48 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe--basedir=/usr
mysql 2887 2782 1 01:48 ? 00:00:00 /usr/sbin/mysqld--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin--log-error=dbmaster1.err --pid-file=dbmaster1.pid
root 2913 2124 0 01:48 pts/0 00:00:00 grep --color=auto mysql
登陆验证修改user表
报错：
[root@dbmaster2 ~]# mysql -u root –p
Enter password:
ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: YES)
解决方法：
[root@dbmaster1 ~]# systemctl stop mysqld.service
[root@dbmaster1 ~]# mysqld_safe--skip-grant-tables
180117 23:31:32 mysqld_safe Logging to'/data/mysqldb/dbmaster2.err'.
180117 23:31:32 mysqld_safe Starting mysqlddaemon with databases from /data/mysqldb
新开一个窗口，到第二个窗口执行
[root@dbmaster1 ~]# mysql
进入数据库
use mysql;
UPDATE user SET Password = PASSWORD('123456')WHERE user = 'root';
FLUSH PRIVILEGES;
杀掉之前登录的会话，在重新启动mysql数据库服务
[root@dbmaster1 usr]# ps -ef|grep mysql
root 3071 3001 0 23:31 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe--skip-grant-tables
mysql 3912 3071 0 23:31 pts/0 00:00:00 /usr/sbin/mysqld--basedir=/usr/lib64/mysql --datadir=/data/mysqldb --plugin-dir=/usr/lib64/mysql/plugin--user=mysql --skip-grant-tables --log-error=dbmaster2.err--open-files-limit=8192 --pid-file=dbmaster2.pid--socket=/var/lib/mysql/mysql.sock --port=3306
root 3946 3016 0 23:33 pts/1 00:00:00 grep --color=auto mysql
[root@dbmaster usr]# kill -9 3071
[root@dbmaster1 usr]# kill -9 3912
[root@dbmaster1 usr]# systemctl startmysqld.service
[root@dbmaster2 usr]# systemctl statusmysqld.service
1.3.4 mysql主机添加复制账号
[root@dbmaster1 usr]#mysql -u root -p
grant REPLICATION SLAVE ON . TO ‘yunqu’@'172.41.176.116'IDENTIFIED BY '123456';
grant REPLICATION SLAVE ON . TO ‘yunqu’@'172.41.176.115'IDENTIFIED BY '123456';
flush privileges;
或者允许所有的IP通过TEST用户连接数据库
grant REPLICATION SLAVE ON . TO ‘yunqu’@'%'IDENTIFIED BY '123456';
flush privileges;
dbmaster2节点：
grantREPLICATION SLAVE ON . TO ‘yunqu’@'172.41.176.115' IDENTIFIED BY '123456';
grant REPLICATION SLAVE ON . TO ‘yunqu’@'172.41.176.116'IDENTIFIED BY '123456';
flush privileges;
或者 允许所有的IP通过TEST用户连接数据库
grant REPLICATION SLAVE ON . TO ‘yunqu’@'%' IDENTIFIED BY'123456';
flush privileges;
主机测试相互验证：看是否能相互访问
Master1 : mysql -u TEST -h 172.41.176.116 -p123456
Master2: mysql -u TEST -h 172.41.176.115 -p123456
1.3.5互告bin-log信息
[root@dbmaster1 usr]# mysql -u root –p
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000006 | 120 | yunqu | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
[root@dbmaster2 mysql-files]# mysql -u root–p
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000004 | 120 | yunqu | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在主机master1中执行：
mysql> change master tomaster_host='172.41.176.116',master_user='yunqu',master_password='123456',master_log_file='mysql-bin.00004',master_log_pos=576;
Query OK, 0 rows affected, 2 warnings (0.02sec)
在主机master2中执行：
change master tomaster_host='172.41.176.115',master_user='yunqu',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=120;
在主机master1、主机master2两服务器都执行以下命令
开启双主状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
验证状态(两主机都执行验证)
mysql> show slave status/G
********* 1. row*********
Slave_IO_State: Waiting for master to send event
Master_Host: 172.41.176.115
Master_User: yunqu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 874
Relay_Log_File: dbmaster2-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: yunqu
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 874
Relay_Log_Space: 460
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3c860faf-fba1-11e7-8107-005056b06744
Master_Info_File: /data/mysqldb/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for theslave I/O thread to update it
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
当看到了两个yes，即：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明已经配置成功了
1.3.6master1创建数据库以及测试数据
在主机master1（192.168.1.2）上创建数据库和表并插入数据
mysql –u root -p
create database yunqu;
use yunqu;
create table test(id int,namevarchar(100));
insert into test value (1,'tom')
在主机master2中验证是否有yunqu 数据库以及表数据
[root@dbmaster2~]# mysql -u root –p
mysql> showdatabases;
+--------------------+
| Database |
+--------------------+
|information_schema |
| mysql |
|performance_schema |
| yunqu |
+--------------------+
4 rows in set(0.00 sec)
mysql> useyunqu;
Database changed
mysql> showtables;
+-----------------+
|Tables_in_yunqu |
+-----------------+
| test |
+-----------------+
1 row in set(0.00 sec)
mysql> select* from test;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+
1 row in set(0.00 sec)
验证成功
1.4 Mycat配置安装
1.4.1 创建分库分表测试数据
注意：在创建数据库之前需要修改my.cnf配置文件，因为我们只设置了同步yunqu数据库,需要添加db01,db02
binlog-do-db = db01
binlog-do-db=db02
然后重启mysql服务
systemctl stop mysqld.service
systemctl start mysqld.service
或者
systemctl restart mysqld.service
再次执行1.3.5步骤
[root@dbmaster1 ~]# mysql -u root -p
mysql> show master status;
+------------------+----------+-----------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-----------------+--------------------------+-------------------+
| mysql-bin.000007 | 120 | yunqu,db01,db02 |mysql,information_schema | |
+------------------+----------+-----------------+--------------------------+-------------------+
1 row in set (0.00 sec)
[root@dbmaster2 ~]# mysql -u root –p
mysql> show master status;
+------------------+----------+-----------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-----------------+--------------------------+-------------------+
| mysql-bin.000005 | 120 | yunqu,db01,db02 |mysql,information_schema | |
+------------------+----------+-----------------+--------------------------+-------------------+
1 row in set (0.00 sec)
发现Fileb变动,需要执行
在主机master1中执行：
mysql> change master tomaster_host='172.41.176.116',master_user='yunqu',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=120;
ERROR 1198 (HY000): This operation cannotbe performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master tomaster_host='172.41.176.116',master_user='yunqu',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01sec)
在主机master2中执行：
mysql>stopslave;
Query OK, 0 rows affected (0.00 sec)
mysql>change master to master_host='172.41.176.115',master_user='yunqu',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01sec)
在主机master1、主机master2两服务器都执行以下命令
开启双主状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
验证状态(两主机都执行验证)
mysql> show slave status/G
最后
创建测试数据
测试要求：
# 有3张表tb1,tb2,tb3和 3 个数据库db01,db02,yunqu
# 现在在3个数据库都创建相同tb1,tb2,tb3三张表
# 表 tb1 数据存储在数据库 db01、表tb2数据分片存储在db01,db02,yunqu、表tb3数据存储在数据库db02
创建语句脚本：
create database db01;
create database db02;
CREATE TABLE tb1 (
id INT NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL default '',
indate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb2 (
id INT NOT NULL AUTO_INCREMENT,
value INT NOT NULL default 0,
indate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULTCHARSET=utf8;
CREATE TABLE tb3 (
id INT NOT NULL AUTO_INCREMENT,
value INT NOT NULL default 0,
name varchar(50) NOT NULL default '',
tb2_id INT NOT NULL,
PRIMARY KEY (id),
key (tb2_id)
)AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULTCHARSET=utf8;
1.4.2 Mycat环境配置
上传mycat软件1.6.5版本到opt目录并解压
tar -xzvfMycat-server-1.6.5-release-20171228230037-linux.tar.gz
下载安装jdk 至少1.7以上
rpm -ivh jdk-8u161-linux-x64.rpm
测试是否安装成功
Java –version
设置Mycat的环境变量
vi /etc/profile
加入两行;
export MYCAT_HOME=/opt/mycat
export PATH=PATH:MYCAT_HOME/bin
使配置文件立即生效，
source /etc/profile
服务器名以及IP绑定
vi /etc/hosts
172.41.176.124 mycat-1
172.41.176.125 mycat-2
创建mycat用户
groupadd mycat
useradd -g mycat mycat
passwd mycat
chown -R mycat:mycat /opt/mycat
设置 wrapper.java.command 的java 路径
查看java环境
whereis java
java: /usr/bin/java /usr/share/man/man1/java.1
vi /opt/mycat/conf/wrapper.conf
wrapper.java.command=/usr/bin/java
进入mycat配置文件目录：
cd /opt/mycat/conf/
备份编辑server.xml
mv server.xml server.xml.bak
cp server.xml.bak server.xml
vi server.xml
这个配置文件主要是服务器的相关配置
<?xml version="1.0"encoding="UTF-8"?>
<!-- - - Licensed under theApache License, Version 2.0 (the "License");
- you may not use this file except incompliance with the License. - You
may obtain a copy of theLicense at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable lawor agreed to in writing, software -
distributed under the License isdistributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OFANY KIND, either express or implied. - See the
License for the specific languagegoverning permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM"server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<!--Mycat相关系统配置 -->
<system>
<propertyname="charset">utf8</property>
<propertyname="defaultSqlParser">druidparser</property>
<propertyname="processors">2</property>
<propertyname="sequnceHandlerType">2</property>
<propertyname="serverPort">8066</property>
<propertyname="managerPort">9066</property>
<propertyname="useGlobleTableCheck">1</property>
<propertyname="mutiNodeLimitType">1</property>
</system>
<!--设置登录mycat的用户名、密码以及schema -->
<username="yunqu1">
<propertyname="password">Password1</property>
<propertyname="schemas">DBTEST1,DBTEST2</property>
<propertyname="benchmark">1000</property>
<propertyname="usingDecrypt">Password1</property>
<!--精细化DML权限 insert,update,select,delete 禁止为0000-->
<!--表级权限：Table级的dml控制,未设置的Table继承schema的dml -->
<privilegescheck="true">
<schemaname="DBTEST1" dml="0110">
<tablename="tbl" dml="0110"></table>
<tablename="tb2" dml="1111"></table>
<tablename="tb3" dml="1001"></table>
</schema>
<schemaname="DBTEST2" dml="1111">
<tablename="tb3" dml="0110"></table>
</schema>
</privileges>
</user>
<username="yunqu2">
<propertyname="password">Password2</property>
<propertyname="schemas">DBTEST1,DBTEST2</property>
<propertyname="readOnly">true</property>
<propertyname="benchmark">1000</property>
<propertyname="usingDecrypt">Password2</property>
</user>
<username="yunqu3">
<propertyname="password">Password3</property>
<propertyname="schemas">DBTEST1,DBTEST2</property>
<propertyname="readOnly">false</property>
<propertyname="benchmark">1000</property>
<propertyname="usingDecrypt">Password3</property>
</user>
/mycat:server
备份编辑schema.xml
mv schema.xml schema.xml.bak
cp schema.xml.bak schema.xml
vi schema.xml
这个配置文件主要是用来配置数据库节点，逻辑表等东西的
<?xmlversion="1.0"?>
<!DOCTYPE mycat:schema SYSTEM"schema.dtd">
mycat:schemaxmlns:mycat="http://io.mycat/"
<!--设置表的存储方式、schema name 与 server.xml设置的schema一致 -->
<schemaname="DBTEST1" checkSQLschema="false"sqlMaxLimit="100" dataNode="dn_db03" >
<tablename="tb1" primaryKey="id" type="global" dataNode="dn_db01" />
<tablename="tb2" primaryKey="id"dataNode="dn_db01,dn_db02,dn_db03" rule="mod-long">
<childTablename="tb3" primaryKey="id" joinKey="tb2_id"parentKey="id" />
</table>
</schema>
<schemaname="DBTEST2" checkSQLschema="false" sqlMaxLimit="100"dataNode="dn_db01">
<tablename="tb3" primaryKey="id" dataNode="dn_db02">
</table>
</schema>
<!--设置dataNode 对应的数据库,及 mycat 连接的地址dataHost(逻辑主机实例) -->
<dataNodename="dn_db01" dataHost="dataHost01"database="db01" />
<dataNode name="dn_db02" dataHost="dataHost02"database="db02" />
<dataNodename="dn_db03" dataHost="dataHost01"database="yunqu" />
<!--mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息
以及 直接定义了具体的数据库实例、读写分离配置和心跳语句 -->
<!--dataHost01 -->
<dataHostname="dataHost01" maxCon="1000" minCon="10"balance="0"
switchType="1"dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1"url="172.41.176.115:3306" user="root"
password="123456" >
<!-- can have multi read hosts -->
<!-- <readHosthost="hostS1" url="localhost:3306" user="root"password="123456"
/> -->
</writeHost>
<writeHosthost="hostM2" url="172.41.176.116:3306"user="root"
password="123456"/>
<!-- can have multi read hosts -->
<!-- <readHosthost="hostS1" url="localhost:3306" user="root"password="123456" /> -->
</dataHost>
<!--dataHost02-->
<dataHostname="dataHost02" maxCon="1000" minCon="10"balance="0"
switchType="1"dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1"url="172.41.176.115:3306" user="root"
password="123456" >
<!-- can have multi read hosts -->
<!-- <readHosthost="hostS1" url="localhost:3306" user="root"password="123456"
/> -->
</writeHost>
<writeHosthost="hostM2" url="172.41.176.116:3306"user="root"
password="123456"/>
<!-- can have multi read hosts -->
<!-- <readHosthost="hostS1" url="localhost:3306" user="root"password="123456"
/> -->
</dataHost>
/mycat:schema
Master1和master2 测试root用户IP登录
报错：
[root@dbmaster1 ~]# mysql -u root -p123456-h 172.41.176.115 -P 3306
Warning: Using a password on the commandline interface can be insecure.
ERROR 1045 (28000): Access denied for user'root'@'172.41.176.115' (using password: YES)
解决方法，授予root用户远程登陆权限：
mysql -u root -p
执行如下SQL语句即可：
grant all privileges on . to 'root'@'%'identified by '123456';
flush privileges;
下载mysql5.6.39客户端并测试
https://dev.mysql.com/downloads/mysql/5.6.html#downloads
MySQL-client-5.6.39-1.el7.x86_64.rpm
报错未安装perl,则需哟啊执行如下命令安装
yum -y install perl perl-devel
再安装mysql 客户端
[root@mycat-1 opt]# rpm -ivhMySQL-client-5.6.39-1.el7.x86_64.rpm
warning:MySQL-client-5.6.39-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:MySQL-client-5.6.39-1.el7 ################################# [100%]
测试访问master1跟master2节点是否成功
[root@mycat-1 opt]# mysql -u root -p123456 -h172.41.176.116-P3306
[root@mycat-1 opt]# mysql -u root -p123456-h172.41.176.115 -P3306
1.4.3 执行mycat
[root@mycat-1~]# su - mycat
Lastlogin: Thu Jan 18 20:57:16 UTC 2018 on pts/3
登录mycat控制台
[mycat@mycat-1~]$ mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Java HotSpot(TM) 64-Bit Server VMwarning: ignoring option MaxPermSize=64M; support was r
jvm 1 | Wrapper (Version 3.2.3)http://wrapper.tanukisoftware.org
jvm 1 | Copyright1999-2006 Tanuki Software, Inc. AllRights Reserved.
jvm 1 |
jvm 1 | log4j:WARN No appenders could be foundfor logger (io.mycat.memory.MyCatMemory).
jvm 1 | log4j:WARN Please initialize the log4jsystem properly.
jvm 1 | log4j:WARN Seehttp://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
jvm 1 | MyCAT Server startup successfully. seelogs in logs/mycat.log
观察mycat.log是否报错：
[mycat@mycat-1 ~]$ cd /opt/mycat/logs/
[mycat@mycat-1 logs]$ vi mycat.log
发现如下报错：
这是因为开启了全局表一致性验证，而创建表mycat 没有自动加上字段_MYCAT_OP_TIME，类型为bigint，自己手工添加
手动添加：
mysql> use db01;
mysql> alter table tb1 add_mycat_op_time bigint;
mysql> use yunqu;
mysql> alter table tb1 add_mycat_op_time bigint;
手工在172.41.176.116上创建与master1同样的表tb1
后续关闭console
在打开的页面ctrl + c
重新执行再观察日志，没有相关错误输出，说明mycat环境没有问题
Mycat.log默认是info级别，通过log4j2.xml可将其设置debug级别，这样就可获得更多有关MyCAT运行的内部信息。
<asyncRoot level="debug" includeLocation="true">
<!--<AppenderRef ref="Console" />-->
<AppenderRef ref="RollingFile"/>
</asyncRoot>
[mycat@mycat-1 ~]$ mycat console
更改为开启mycat
[mycat@mycat-1 ~]$ mycat start
Starting Mycat-server...
[mycat@mycat-1 ~]$ ps -ef|grep mycate
1.4.4mycat2配置
传相关配置到mycat2节点
[root@mycat-1 conf]# scp schema.xml172.41.176.125:/opt/mycat/conf/
root@172.41.176.125's password:
schema.xml 100% 2685 2.6KB/s 00:00
[root@mycat-1 conf]# scp server.xml172.41.176.125:/opt/mycat/conf/
root@172.41.176.125's password:
server.xml 100% 2256 2.2KB/s 00:00
mycat 环境配置参考见1.4.2步骤相同，这里省略不写………
[mycat@mycat-2 conf]$ mycat console
RunningMycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Java HotSpot(TM) 64-Bit Server VMwarning: ignoring option MaxPermSize=64M; support was removed in 8.0
jvm 1 | Wrapper (Version 3.2.3)http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | log4j:WARN No appenders could be foundfor logger (io.mycat.memory.MyCatMemory).
jvm 1 | log4j:WARN Please initialize the log4jsystem properly.
jvm 1 | log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfigfor more info.
jvm 1 | MyCAT Server startup successfully. seelogs in logs/mycat.log
观察查看mycat.log日志显示正常…
Mycat.log默认是info级别，通过log4j2.xml可将其设置debug级别，这样就可获得更多有关MyCAT运行的内部信息。
<asyncRoot level="debug"includeLocation="true">
<!--<AppenderRef ref="Console" />-->
<AppenderRef ref="RollingFile"/>
</asyncRoot>
停止console,启动mycat服务
[mycat@mycat-2 conf]$ mycat start
Starting Mycat-server...
[mycat@mycat-2 conf]$
[root@mycat-2 logs]# mysql -uyunqu1 -p123456-h127.0.0.1 -P8066
Warning: Using a password on the command lineinterface can be insecure.
ERROR 1045 (HY000): Access denied for user'yunqu1', because password is error
[root@mycat-2 logs]# mysql -uyunqu1 -pPassword1-h127.0.0.1 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DBTEST1 |
| DBTEST2 |
+----------+
2 rows in set (0.01 sec)
mysql> exit
[root@mycat-2 logs]# mysql -uyunqu1-pPassword1 -h127.0.0.1 -P9066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DBTEST1 |
| DBTEST2 |
+----------+
2 rows in set (0.00 sec)
mysql> exit
试着用yunqu2用户登录连接，连接也是没有问题的，都正常……这里就省略过程
1.4.5 验证mycat
根据前面的scheme.xml以及server.xml可知：
物理： 表 tb1 数据存储在数据库 db01
表tb2数据分片存储在db01,db02,yunqu、
表tb3数据存储在数据库db02
mycat：
逻辑结构
说明：
表级权限：insert,update,select,delete 所有都禁止操作为0000
用户 密码 逻辑库 逻辑表 服务器端口 管理端口 表级权限 事实表 数据存储地(mysql数据库)
yunqu3 Password3 DBTEST1 ALL 8066 9066 所有权限 yunqu
DBTEST2 ALL 所有权限 db01
yunqu2 Password2 DBTEST1 DBTEST2 ALL Tables 8066 9066 只读 Readonly ALL ALL Db
yunqu1 Password1 DBTEST2 tb3 8066 9066 0110 tb3 db02
DBTEST1 tb1 0110 tb1 db01
tb2 1111 tb2 db01,db02,yunqu
tb3 1001 tb3 db02
最终存储结果：
Mysql数据库中
tb1 在db01（包括其他db02,yunqu数据库也无数据）中没有数据
tb2 数据分别存储在db01,db02,yunqu
tb3 数据存储在db02
Mycat中操作插入数据：
插入语句SQL
insert into tb1(name,indate)values('kk',now());
insert into tb2(id,value,indate)values(1,100,now());
insert into tb3(value,name,tb2_id)values('pad',40,1);
insert into tb3(value,name,tb2_id)values('phone',50,1);
insert into tb2(id,value,indate)values(999,100,now());
insert into tb3(value,name,tb2_id)values(1,40,999);
insert into tb3(value,name,tb2_id)values(2,50,999);
在mycat1中登录：(同样在mycat2中登录测试，结果是一样的….这里就省略测试结果)
测试yunqu1用户
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DBTEST1 |
| DBTEST2 |
+----------+
2 rows in set (0.00 sec)
mysql>use DBTEST1;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> insert into tb1(name,indate)values('kk',now());
ERROR 3012 (HY000): The statement DML privilege check isnot passed, reject for user 'yunqu1'
mysql> insert into tb2(id,value,indate)values(3,100,now());
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb2(id,value,indate)values(4,100,now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb3(value,name,tb2_id)values('yunqu',50,1);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into tb3(value,name,tb2_id)values('yunqu',90,2);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>use DBTEST2;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> insert into tb1(name,indate)values('kk',now());
ERROR 1064 (HY000): can't find table define in schema TB1schema:DBTEST2
mysql> insert into tb2(id,value,indate)values(1,100,now());
ERROR 1064 (HY000): can't find table define in schema TB2schema:DBTEST2
mysql> insert into tb3(value,name,tb2_id)values('yunqu',50,1);
ERROR 3012 (HY000): The statement DML privilege check isnot passed, reject for user 'yunqu1'
测试yunqu2用户只读：这里只显示了DBTEST1结果
[mycat@mycat-1 ~]$ mysql -uyunqu2 -pPassword2 -h127.0.0.1-P8066
mysql> use DBTEST1;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> insert into tb2(id,value,indate)values(1,100,now());
ERROR 1495 (HY000): User readonly
mysql> delte from tb2;
ERROR 1495 (HY000): User readonly
mysql> update tb2 set value='ss' where id=1;
ERROR 1495 (HY000): User readonly
Mysql 数据库中查看数据是否分不正确
Mysql数据库中
tb1 在db01（包括其他数据库）中没有数据
mysql> usedb01;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb1;
Empty set (0.00 sec)
mysql> use db02;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb1;
Empty set (0.00 sec)
mysql> use yunqu;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb1;
Empty set (0.00 sec)
tb2 数据分别存储在db01,db02,yunqu
mysql> use db01
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb2;
+----+-------+---------------------+
| id | value | indate |
+----+-------+---------------------+
| 3 | 100 | 2018-01-19 11:24:34 |
+----+-------+---------------------+
1 row in set (0.01 sec)
mysql> use db02;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb2;
+----+-------+---------------------+
| id | value | indate |
+----+-------+---------------------+
| 1 | 100 | 2018-01-19 11:13:10 |
| 4 | 100 | 2018-01-19 11:24:39 |
+----+-------+---------------------+
2 rows in set (0.00 sec)
mysql> use yunqu;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb2;
+----+-------+---------------------+
| id | value | indate |
+----+-------+---------------------+
| 2 | 100 | 2018-01-19 11:15:40 |
+----+-------+---------------------+
1 row in set (0.00 sec)
tb3 数据存储在db02
mysql> use db01;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb3;
Empty set (0.00 sec)
mysql> use db02;
Reading table information for completion of table and columnnames
You can turn off this feature to get a quicker startupwith -A
Database changed
mysql> select * from tb3;
+----+-------+------+--------+
| id | value | name | tb2_id |
+----+-------+------+--------+
| 1 | 0 | 40 | 1 |
| 3 | 0 | 50 | 1 |
| 5 | 0 | 50 | 1 |
+----+-------+------+--------+
3 rows in set (0.00 sec)
mysql> use yunqu;
Reading table information for completion of table andcolumn names
You can turn off this feature to get a quicker startupwith -A
mysql> select * from tb3;
Empty set (0.00 sec)
Mysql 2节点同样执行上述验证步骤，看结果是否正确…
这里master2验证结果正确，步骤省略…自行验证
1.5 安装keepalived
这里keepalived、ipvsadm软件要另外准备两台主机服务器安装（冗余），不能跟mycat、mysql服务器安装到一起，会出现问题….无法使用虚拟IP的问题
（我这里就尝试把keepalived、ipvsadm跟mycat安装再一起，结果就失败了，无法使用虚拟IP）
注意：以下操作都需到两台主机服务器上执行
1.5.1 安装nc插件
安装keepalived之前首先安装nc插件。用来对Mycat的8066端口做心跳检测
yum install nc
或者
yum install nmap-ncat.x86_64
1.5.2 安装keepalived
yum install keepalived ipvsadm
查看keepalived路径
whereis keepalived
keepalived: /usr/sbin/keepalived /etc/keepalived/usr/libexec/keepalived /usr/share/man/man8/keepalived.8.gz
keepalived.conf文件说明
配置keepalived.conf：
global_defs {
notification_email {
root@localhost
}
notification_email_fromroot@localhost
smtp_server localhost
smtp_connect_timeout 30
router_id NodeA
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
默认的配置文件中，使用第三方smtp服务器，但这在现实中几乎没有意义（需要验证的原因），我们将其指定为localhost, 将通知信息的发送交给本地sendmail服务处理。查阅说明文档得知route_id配置是为了标识当前节点，我将其设置为NodeA。
vrrp_instance VI_1 {
state MASTER #指定A节点为主节点备用节点上设置为BACKUP即可
interface eth0 #绑定虚拟IP的网络接口
virtual_router_id 51 #VRRP组名，两个节点的设置必须一样，以指明各个节点属于同一VRRP组
priority 100 #主节点的优先级（1-254之间），备用节点必须比主节点优先级低
advert_int 1 #组播信息发送间隔，两个节点设置必须一样
authentication { #设置验证信息，两个节点必须一致
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { #指定虚拟IP, 两个节点设置必须一样。
192.168.118.16 #测试过程中发现，该虚拟ip只有和真实ip在同一网段，才好用
#不知道是不是跟我的虚拟机用的nat模式联网有关。
#测试主机A节点ip为192.168.118.128
}
}
配置keepalived相关文件
cd /etc/keepalived/
vi keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from root@localhost
smtp_serverlocalhost
smtp_connect_timeout 30
router_id NodeA
}
vrrp_instance VI_1 {
state MASTER
interfaceens160
virtual_router_id 51
priority 100
advert_int 1
authentication{
auth_typePASS
auth_pass1111
}
virtual_ipaddress {
172.41.176.119
}
}
virtual_server 172.41.176.119 8066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 0
protocol TCP
nat_mask255.255.255.0
real_server172.41.176.124 8066 {
weight 1
# realserver的状态检测设置部分，单位是秒
TCP_CHECK {
connect_timeout 3 #表示3秒无响应超时 delay_before_retry3 #表示重试间隔
connect_port 8066
}
}
real_server172.41.176.125 8066 {
weight 1
TCP_CHECK {
connect_timeout 3
delay_before_retry 3
connect_port 8066
}
}
}
按同样的方法配置节点B并修改配置文件，可将A节点的配置文件复制到B节点，并修改以下几项：
router_id NodeB
state BACKUP
priority 90
1.5.4在MyCAT服务器上为lo:0绑定VIP地址、抑制ARP广播
分别在mycat-1和mycat-2两台主机上创建执行以下脚本
vi /opt/mycat/realserver.sh
#!/bin/bash
#description: Config realserver
VIP=172.41.176.119
/etc/rc.d/init.d/functions
case "$1" in
start)
/sbin/ifconfig lo:0 VIP netmask 255.255.255.255 broadcast VIP
/sbin/routeadd -host $VIP dev lo:0
echo"1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo"2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo"1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo"2" >/proc/sys/net/ipv4/conf/all/arp_announce
sysctl -p>/dev/null 2>&1
echo"RealServer Start OK"
;;
stop)
/sbin/ifconfiglo:0 down
/sbin/routedel $VIP >/dev/null 2>&1
echo"0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo"0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo"0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo"0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo"RealServer Stoped"
;;
*)
echo"Usage: $0 {start|stop}"
exit 1
esac
exit 0
[root@mycat-1~]# sh realserver.sh start
[root@mycat-2~]# sh realserver.sh start
执行命令 ip adrr 查看VIP是否绑定成功（注意ifconfig命令无法查看到配置的虚拟IP）
mycat-1: ip addr
[root@mycat-1 logs]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdiscnoqueue state UNKNOWN qlen 1
link/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00
inet127.0.0.1/8 scope host lo
valid_lftforever preferred_lft forever
inet 172.41.176.119/32 brd172.41.176.119 scope global lo:0
valid_lftforever preferred_lft forever
inet6 ::1/128scope host
valid_lftforever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu1500 qdisc mq state UP qlen 1000
link/ether00:50:56:b0:f3:37 brd ff:ff:ff:ff:ff:ff
inet172.41.176.124/24 brd 172.41.176.255 scope global ens160
valid_lftforever preferred_lft forever
inet6fe80::3250:fe2a:c8ce:83d6/64 scope link
valid_lftforever preferred_lft forever
mycat-2: ip addr
[root@mycat-2 mycat]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdiscnoqueue state UNKNOWN qlen 1
link/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00
inet127.0.0.1/8 scope host lo
valid_lftforever preferred_lft forever
inet 172.41.176.119/32 brd172.41.176.119 scope global lo:0
valid_lftforever preferred_lft forever
inet6 ::1/128scope host
valid_lftforever preferred_lft forever
2: ens160: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu1500 qdisc mq state UP qlen 1000
link/ether00:50:56:b0:e2:25 brd ff:ff:ff:ff:ff:ff
inet172.41.176.125/24 brd 172.41.176.255 scope global ens160
valid_lftforever preferred_lft forever
inet6fe80::fee0:cdfe:2592:9453/64 scope link
valid_lftforever preferred_lft forever
1.5.5启动keepalived服务并验证
systemctl enable keepalived.service
systemctl start keepalived.service
ipvsadm –help 验证ipvsadm是否安装成功
用ipvsadm -L查看Keepalived路由信息****
** **
[root@mycat-1keepalived]# ipvsadm -L
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
->RemoteAddress:Port ForwardWeight ActiveConn InActConn
TCP Monitor7:8066rr
->172.41.176.124:8066 Masq 1 0 0
->172.41.176.125:8066 Masq 1 0 0
[root@mycat-2keepalived]# ipvsadm -L
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
->RemoteAddress:Port ForwardWeight ActiveConn InActConn
TCP 172.41.176.119:8066 rr
->172.41.176.124:8066 Masq 1 0 0
->172.41.176.125:8066 Masq 1 0 0
从上面看显示都正常…
1.5.6 mycat中验证
登录数据库
[root@mycat-1keepalived]# mysql -uyunqu3 -pPassword3 -h172.41.176.119 -P8066
Warning: Using apassword on the command line interface can be insecure.
Welcome to the MySQLmonitor. Commands end with ; or /g.
Your MySQL connectionid is 1423
Server version:5.6.29-mycat-1.6.5-release-20171228230037 MyCat Server (OpenCloundDB)
Copyright (c) 2000,2018, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered trademark of Oracle Corporation and/or its
affiliates. Othernames may be trademarks of their respective
owners.
Type 'help;' or '/h'for help. Type '/c' to clear the current input statement
mysql> showdatabases;
+----------+
| DATABASE |
+----------+
| DBTEST1 |
| DBTEST2 |
+----------+
2 rows in set (0.00sec)
mysql> useDBTEST1;
Reading tableinformation for completion of table and column names
You can turn off thisfeature to get a quicker startup with -A
Database changed
mysql> showtables;
+-----------------+
| Tables_in_yunqu |
+-----------------+
| tb1 |
| tb2 |
| tb3 |
| tb4 |
| test |
+-----------------+
5 rows in set (0.00sec)
mysql>create table tb5 (id int);
Query OK, 0 rowsaffected (0.01 sec)
mysql> insert intotb5 values(1);
Query OK, 1 rowsaffected (0.01 sec)
查看Mycat-2是否有创建的表
[root@mycat-2 mycat]# mysql -uyunqu3-pPassword3 -h172.41.176.119 -P8066
Warning: Using apassword on the command line interface can be insecure.
Welcome to the MySQLmonitor. Commands end with ; or /g.
Your MySQL connectionid is 1498
Server version:5.6.29-mycat-1.6.5-release-20171228230037 MyCat Server (OpenCloundDB)
Copyright (c) 2000,2018, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered trademark of Oracle Corporation and/or its
affiliates. Othernames may be trademarks of their respective
owners.
Type 'help;' or '/h'for help. Type '/c' to clear the current input statement.
mysql>
mysql> useDBTEST1;
Reading tableinformation for completion of table and column names
You can turn off thisfeature to get a quicker startup with -A
Database changed
mysql> showtables;
+-----------------+
| Tables_in_yunqu |
+-----------------+
| tb1 |
| tb2 |
| tb3 |
| tb4 |
| tb5 |
| test |
+-----------------+
6 rows in set (0.00sec)
mysql> select *from tb5;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.08sec)
说明正常…..
最后，我们再去mysql数据库中查看数据是否被yunqu数据库存储了..
[root@dbmaster1~]# mysql -uroot -p123456
Warning: Using apassword on the command line interface can be insecure.
Welcome to the MySQLmonitor. Commands end with ; or /g.
Your MySQL connectionid is 320
Server version:5.6.39-log MySQL Community Server (GPL)
Copyright (c) 2000,2018, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered trademark of Oracle Corporation and/or its
affiliates. Othernames may be trademarks of their respective
owners.
Type 'help;' or '/h'for help. Type '/c' to clear the current input statement.
mysql> use yunqu;
Reading tableinformation for completion of table and column names
You can turn off thisfeature to get a quicker startup with -A
Database changed
mysql> showtables;
+-----------------+
| Tables_in_yunqu |
+-----------------+
| tb1 |
| tb2 |
| tb3 |
| tb4 |
| tb5 |
| test |
+-----------------+
6 rows in set (0.00sec)
mysql> select *from tb5;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00sec)
[root@dbmaster2~]# mysql -uroot -p
Enter password:
Welcome to the MySQLmonitor. Commands end with ; or /g.
Your MySQL connectionid is 111
Server version:5.6.39-log MySQL Community Server (GPL)
Copyright (c) 2000,2018, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered trademark of Oracle Corporation and/or its
affiliates. Othernames may be trademarks of their respective
owners.
Type 'help;' or '/h'for help. Type '/c' to clear the current input statement.
mysql> use yunqu;
Reading tableinformation for completion of table and column names
You can turn off thisfeature to get a quicker startup with -A
Database changed
mysql> select *from tb5;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00sec)
mysql> showtables;
+-----------------+
| Tables_in_yunqu |
+-----------------+
| tb1 |
| tb2 |
| tb3 |
| tb4 |
| tb5 |
| test |
+-----------------+
6 rows in set (0.00sec)
数据正常,至此所有的环境搭建都正常完成…
版权声明：本文为博主原创文章，未经博主允许不得转载。
