1.数据库集群
1.1主从复制(一主一从)
原理
搭建mysql(Percona5.6)
配置主从节点
1.2数据库高可用中的各种概念
故障转移
读写分离
热备模式
单机热备
双机热备
中间件的维护中实现这些内容
2.mycat
2.1mycat介绍
2.2特点
2.3同类产品比较和假死的原理
2.4mycat入门案例
逻辑库
逻辑表
非分片表
分片表
全局表
ER分片表
Linux安装Percona的mysql
https://www.percona.com/downloads/Percona-Server-5.6/LATEST/【网站】
安装:
[root@localhost02 percona]# rpm -ivh Percona-Server-56-debuginfo-5.6.24-rel72.2.el6.x86_64.rpm
warning: Percona-Server-56-debuginfo-5.6.24-rel72.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-56-debug########################################### [100%]
[root@localhost03 percona]# rpm -ivh Percona-Server-shared-56-5.6.24-rel72.2.el6.x86_64.rpm
warning: Percona-Server-shared-56-5.6.24-rel72.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-shared-5########################################### [100%]
[root@localhost03 percona]# rpm -ivh Percona-Server-client-56-5.6.24-rel72.2.el6.x86_64.rpm
warning: Percona-Server-client-56-5.6.24-rel72.2.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ########################################### [100%]
1:Percona-Server-client-5########################################### [100%]
[root@localhost03 percona]# rpm -ivh Percona-Server-server-56-5.6.24-rel72.2.el6.x86_64.rpm
连接数据库【使用用户名和密码】
[root@localhost03 mysql]# mysql -uroot -proot
注意:
问题【设置用户名和密码错误----kill 】
[root@localhost02 mysql]# mysqladmin -u root password root
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
缺少文件localhost02.pid mysql.sock
[root@localhost02 ~]# cd /var/lib/mysql
[root@localhost02 mysql]# ls
auto.cnf ib_logfile0 mysql RPM_UPGRADE_HISTORY test
ibdata1 ib_logfile1 performance_schema RPM_UPGRADE_MARKER-LAST
杀死进程【成功解决问题】
[root@localhost02 mysql]# ps -ef|grep mysql
root 1240 1 0 18:56 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost02.pid
mysql 1404 1240 0 18:56 ? 00:00:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost02.pid --socket=/var/lib/mysql/mysql.sock
root 1984 1 0 19:04 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost02.pid
mysql 2148 1984 0 19:04 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost02.pid --socket=/var/lib/mysql/mysql.sock
root 2179 1602 0 19:06 pts/0 00:00:00 grep mysql
[root@localhost02 mysql]# kill 1240
[root@localhost02 mysql]# kill 1404
[root@localhost02 mysql]# kill 1984
-bash: kill: (1984) - 没有那个进程
[root@localhost02 mysql]# kill 2148
-bash: kill: (2148) - 没有那个进程
[root@localhost02 mysql]# kill 2179
-bash: kill: (2179) - 没有那个进程
[root@localhost02 mysql]# kill 1602
[root@localhost02 mysql]# ps -ef|grep mysql
root 2189 1602 0 19:07 pts/0 00:00:00 grep mysql
[root@localhost02 mysql]# service mysql start
Starting MySQL (Percona Server).. [确定]
[root@localhost02 mysql]# ls
auto.cnf ib_logfile1 mysql.sock RPM_UPGRADE_MARKER-LAST
ibdata1 localhost02.pid performance_schema test
ib_logfile0 mysql RPM_UPGRADE_HISTORY
[root@localhost02 mysql]# mysqladmin -u root password root
Warning: Using a password on the command line interface can be insecure.
登录
[root@localhost02 mysql]# mysql -uroot -proot
mysql> grant all on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)
注意:
navicat可能连接不成功【防火墙开启了----关闭即可】
主从的结构配置
localhost2 主节点 localhost3为从节点
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin=mysql-log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-log.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
position:指针的值
[root@localhost03 etc]# vim my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2
log-bin=mysql-log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
可以使用中间件管理数据库集群
中间件管理数据库集群的概念
#SLAVE 192.168.253.130
START SLAVE
STOP SLAVE
show slave status;
#挂接从节点到主节点
CHANGE MASTER TO
MASTER_HOST='192.168.253.129',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-log.000003',
MASTER_LOG_POS=120
双机热备时是数据库集群中数据分片高可用的基础结构,一般数据库集群中几个数据分片的高可用只需要配置双机热备
mycat中间件
安装mycat测试入门案例
mycat网站
http://dl.mycat.org.cn/
[root@localhost02 ~]# cp Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz /home/software
[root@localhost02 software]# tar -xf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz
1.2软件结构
[root@localhost02 mycat]# ll
总用量 24
drwxr-xr-x. 2 root root 4096 5月 14 12:50 bin
drwxrwxrwx. 2 root root 4096 12月 13 2015 catlet
drwxrwxrwx. 2 root root 4096 5月 14 12:50 conf
drwxr-xr-x. 2 root root 4096 5月 14 12:50 lib
drwxrwxrwx. 2 root root 4096 12月 13 2015 logs
-rwxrwxrwx. 1 root root 219 11月 30 2016 version.txt
启动mycat报错
[root@localhost02 bin]# ./mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
wrapper | JVM exited while loading the application.
jvm 1 | 错误: 代理抛出异常错误: java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: localhost02: localhost02: 未知的名称或服务
解决---->改动localhost02为localhost
双机热备
在原来的从节点中写语句去连接原来的主节点
#下载Linux中的文件到windows,选择路径即可【可以用xftp】
[root@localhost conf]# sz server.xml.back
server.xml【服务mycat有关的配置】
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
</system>
<user name="root">
<property name="password">root</property>
<property name="schemas">mstest</property>
</user>
</mycat:server>
schema.xml【逻辑库逻辑表分片等详细配置文件】
启动mycat
[root@localhost conf]# ../bin/mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
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 2021-05-14 16:43:38 [./conf/log4j.xml] load completed.
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
linux登录mycat
先执行
[root@localhost conf]# ../bin/mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
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 2021-05-14 17:04:06 [./conf/log4j.xml] load completed.
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
再执行【在其它的Linux上执行也可以】
#mysql -uroot -proot -P8066 -h192.168.253.129(mycat所在的ip地址)
[root@localhost03 ~]# mysql -uroot -proot -P8066 -h192.168.253.129
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> insert into mstest.user (id,name) values("3","王府井");
Query OK, 1 row affected (0.04 sec)
mysql> select * from mstest.user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 曹阳 |
| 2 | 张帅 |
| 3 | 王府井 |
+----+-----------+
3 rows in set (0.00 sec)
注意问题:
mysql> select * from mstest.user;
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0
需要开启192.168.253.129主节点(安装mycat的节点)
[root@localhost mysql]# service mysql start
Starting MySQL (Percona Server).. [确定]
[root@localhost mysql]# mysql -uroot -proot
总结:
开启MySQL服务,再开启mycat服务,即可用mycat来连接MySQL
任务栏网络不见了