MySQL数据库主从复制、读写分离和备份

通过编译、二进制安装MySQL5.7

1、安装相应工具包
yum  -y install libaio numactl-libs wget

2、准备启动用户
groupadd mysql
useradd -r -M -g mysql -s /bin/false mysql	# -r创建系统用户，-M不创建用户家目录 -s指定用户不可登录

3、下载MySQL源码包解压并修改权限
wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local/
ln -s mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
chown -R mysql.mysql /usr/local/mysql/

4、准备环境变量
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

5、准备配置文件
cp /etc/my.cnf{,.bak}
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

6、初始化MySQL并设置root账号密码为空
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql

7、准备服务脚本并启动MySQL
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start

8、设置root账号密码
mysqladmin -uroot   password magedu

9、测试登录
mysql -uroot -pmagedu

二进制安装mariadb10.4

1、下载二进制包并添加启动账号
wget https://mirrors.bkns.vn/mariadb//mariadb-10.4.25/bintar-linux-systemd-x86_64/mariadb-10.4.25-linux-systemd-x86_64.tar.gz
groupadd mysql
useradd -r -M -g mysql -s /bin/false mysql	# -r创建系统用户，-M不创建用户家目录 -s指定用户不可登录

2、安装mariadb-server
tar -xf mariadb-10.4.25-linux-systemd-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -s mariadb-10.4.25-linux-systemd-x86_64 mysql
cd mysql
./scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql  #指定数据存放目录，此处使用/etc/my.cnf中的默认配置
chown -R mysql.mysql .

3、启动mysql并配置环境变量
export PATH=$PATH:/usr/local/mysql/bin/   #临时生效，永久生效配置参考MySQL5.7的安装步骤

4、准备服务开机自启文件
cp support-files/systemd/mariadb.service /usr/lib/systemd/system/mariadb.service
systemctl daemon-reload

7、启动mariadb-server
systemctl start mariadb.service 

8、设置开机自启
systemctl enable mariadb.service 

9、连接mysql测试
[root@localhost mysql]# mysql 	#直接输入mysql报错，此处是因为二进制安装的mariadb的客户端命令mysql将/tmp/mysql.sock 作为默认路径，而当前/etc/my.cnf配置的路径为/var/lib/mysql/mysql.sock
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

手动指定sock文件路径即可登录成功（sock文件的作用读者可自行查阅相关资料）
[root@localhost mysql]# mysql --sock=/var/lib/mysql/mysql.sock
Info: Using unique option prefix 'sock' is error-prone and can break in the future. Please use the full name 'socket' instead.
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.25-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

MariaDB [hellodb]> desc students;         --查看表结构
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| StuID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)         | NO   |     | NULL    |                |
| Age       | tinyint(3) unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')       | NO   |     | NULL    |                |
| ClassID   | tinyint(3) unsigned | YES  |     | NULL    |                |
| TeacherID | int(10) unsigned    | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> select name,age from students where age >25 and gender='M';
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.00 sec)

(2) 以ClassID为分组依据，显示每组的平均年龄

MariaDB [hellodb]> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
8 rows in set (0.00 sec)

(3) 显示第2题中平均年龄大于30的分组及平均年龄

MariaDB [hellodb]> select classid,avg(age) avg_age from students group by classid having avg_age > 30;
+---------+---------+
| classid | avg_age |
+---------+---------+
|    NULL | 63.5000 |
|       2 | 36.0000 |
|       5 | 46.0000 |
+---------+---------+
3 rows in set (0.00 sec)

(4) 显示以L开头的名字的同学的信息

MariaDB [hellodb]> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

数据库授权root用户，允许172.16.16.0/24网段可以连接mysql（注意关闭防火墙）
查看当前数据库用户，观察到没有授权远程登陆账号

创建用户root@'172.16.16.%' 并设置密码后验证登陆

主从复制及主主复制的实现（注意关闭防火墙）
1、主从复制

系统：centos7.6  数据库 5.5.68-MariaDB 

主节点配置(172.16.16.128)：
systemctl stop firewalld
yum install -y mariadb-server   #注意：centos7.6系统官方仓库未提供mysql-server源，因此安装mariadb作为代替

[root@master ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=8
log-bin

[root@master ~]# mysql
MariaDB [(none)]> show master logs;  --查看二进制文件位置
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       418 |
| mariadb-bin.000002 |       688 |
+--------------------+-----------+
2 rows in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.16.%' identified by 'passwd'; --#新建账号 repluser密码passwd并授权
Query OK, 0 rows affected (0.00 sec)

--如果是MySQL 8.0 创建账号和授权需要分成下面两步实现
mysql>create user 'repluser'@'172.16.16.%';
mysql>grant replication slave on *.* to 'repluser'@'172.16.16.%';


从节点配置(172.16.16.28):
systemctl stop firewalld
[root@slave ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=18
log-bin

systemctl start mariadb-server
[root@slave ~]# mysql
MariaDB [(none)]> change master to 
	-> master_host='172.16.16.128',
	-> master_user='repluser',
	-> master_password='passwd',
	-> master_port=3306,
	-> master_log_file='mariadb-bin.000002',
	-> master_log_pos=245;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 172.16.16.128
				  Master_User: repluser
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mariadb-bin.000002
		  Read_Master_Log_Pos: 996
			   Relay_Log_File: mariadb-relay-bin.000002
				Relay_Log_Pos: 839
		Relay_Master_Log_File: mariadb-bin.000002
			 Slave_IO_Running: Yes	--I/O线程正常运行
			Slave_SQL_Running: Yes	--sql线程正常运行
			  Replicate_Do_DB: 
		  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: 996
			  Relay_Log_Space: 1135
			  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	--主从复制延迟为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: 8
1 row in set (0.00 sec)

2、主主复制

-- master1(172.16.16.128)配置:
[root@master1 ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=8
log-bin
auto_increment_offset=1
auto_increment_increment=2
[root@master1 ~]# systemctl start mariadb
[root@master1 ~]# mysql
MariaDB [(none)]> show master logs;  #注意：先查看bin-log日志后创建账号，开启同步后，账号也会被同步到从节点，反之不行
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       418 |
| mariadb-bin.000002 |       996 |
+--------------------+-----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to repluser@'172.16.16.%' identified by 'passwd';  #创建用于主从同步的账号并授权
Query OK, 0 rows affected (0.00 sec)

-- master2(172.16.16.28)配置:
[root@master2 ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=18
log-bin
auto_increment_offset=2
auto_increment_increment=2
[root@master2 ~]# systemctl start mariadb
[root@master2 ~]# mysql
MariaDB [(none)]> change master to
	->  master_host='172.16.16.128',
	->  master_user='repluser',
	->  master_password='passwd',
	->  master_port=3306,
	->  master_log_file='mariadb-bin.000002',	#从master1获取
	->  master_log_pos=996;  #从master1获取
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> show slave status\G;	master2向master1同步数据成功
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 172.16.16.128
				  Master_User: repluser
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mariadb-bin.000002
		  Read_Master_Log_Pos: 1150
			   Relay_Log_File: mariadb-relay-bin.000002
				Relay_Log_Pos: 685
		Relay_Master_Log_File: mariadb-bin.000002
			 Slave_IO_Running: Yes	
			Slave_SQL_Running: Yes
...省略...

MariaDB [(none)]> show master logs;	#查看日志名和偏移量为master1向master2同步数据作准备
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       418 |
| mariadb-bin.000002 |       264 |
| mariadb-bin.000003 |       245 |
+--------------------+-----------+
3 rows in set (0.00 sec)

-- 在master1执行（此时master2已同步repluser@'172.16.16.%'账号，故不需在master2手动创建）
MariaDB [(none)]> change master to
	->  master_host='172.16.16.28',
	->  master_user='repluser',
	->  master_password='passwd',
	->  master_port=3306,
	->  master_log_file='mariadb-bin.000003',  
	->  master_log_pos=245;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;	master1向master2同步数据成功
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 172.16.16.28
				  Master_User: repluser
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mariadb-bin.000003
		  Read_Master_Log_Pos: 245
			   Relay_Log_File: mariadb-relay-bin.000002
				Relay_Log_Pos: 531
		Relay_Master_Log_File: mariadb-bin.000003
			 Slave_IO_Running: Yes
			Slave_SQL_Running: Yes
...

xtrabackup实现全量+增量+binlog恢复库
实验环境：系统：centos7.6（注意：centos8的yum源未提供xtrabackup）数据库 5.5.68-MariaDB

-- source主机（172.16.16.128）操作：
1 备份过程
1）完全备份：
[root@source ~]#yum -y install percona-xtrabackup  #准备备份工具
[root@source ~]#mkdir /backup/  #准备目录用于数据备份
[root@source ~]#xtrabackup --user=root --password=passwd --backup --target-dir=/backup/base  #准备第一次全量备份
2）第一次修改数据
[root@source ~]#mysql -uroot -ppasswd -e 'create database inc1' #新建数据库inc1
3）第一次增量备份
[root@source ~]#xtrabackup --user=root --password=passwd --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
4）第二次修改数据
[root@source ~]#mysql -uroot -ppasswd -e 'create database inc2' #新建数据库inc2
5）第二次增量备份
[root@source ~]#xtrabackup --user=root --password=passwd  --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 （基于第一次增量备份做增量备份
#备份结束生成三个备份目录
[root@source ~]# ls /backup/
base  inc1  inc2
#原数据库有hellodb,inc1,inc2三个数据库
[root@source ~]# mysql -uroot -ppasswd -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| inc1               |
| inc2               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6）将备份传输到目标主机
[root@source ~]#scp -r /backup/* 172.16.16.28:/backup/

-- dest主机（172.16.16.28）操作：
2还原过程
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@dest ~]#yum -y install percona-xtrabackup
[root@dest ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份
[root@dest ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@dest ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@dest ~]systemctl stop mariadb
[root@dest ~]#xtrabackup --copy-back --target-dir=/backup/base
5）还原属性：
[root@dest ~]#chown -R mysql:mysql /var/lib/mysql
6）启动服务：
[root@dest ~]#systemctl start mariadb
[root@dest ~]# mysql -uroot -ppasswd -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| inc1               |
| inc2               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

-- bin-log日志还原数据
假设source主机（172.16.16.128）在做完第二次增量备份（/backup/inc2)且第三次增量备份（inc3）还没做的期间发生了如下数据变动：
#test1和test3被新建，而hellodb因操作失误被删除
[root@source ~]# mysql -uroot -ppasswd -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bin_log_test       |
| inc1               |
| inc2               |
| mysql              |
| performance_schema |
| test               |
| test1              |
| test3              |
+--------------------+

在source主机开启了二进制日志的情况下可以通过以下步骤还原test1和test3：
1）还原上一次备份（如步骤2在dest主机所做）
2）查看上一次备份（inc2）记录的二进制日志文件名和偏移量
[root@source ~]# cat /backup/inc2/xtrabackup_binlog_info 
mariadb-bin.000001	8179

3）将该位置往后的二进制日志还原为sql备份（在source主机执行）
mysqlbinlog --start-position=8179 /var/lib/mysql/mariadb-bin.000001 > /backup/bin.sql

4）编辑backup/bin.sql文件，删除其中的drop database hellodb
[root@source mysql]# grep 'drop database hellodb' /backup/bin.sql 
drop database hellodb
[root@source mysql]# sed -i '/drop database hellodb/d' /backup/bin.sql  #删除该sql语句
[root@source mysql]# grep 'drop database hellodb' /backup/bin.sql #确认删除成功

5）在dest主机执行还原语句
[root@dest ~]# mysql -uroot -ppasswd < /backup/bin.sql 
[root@dest ~]# mysql -uroot -ppasswd -e 'show databases' #可以发现test1和test3还原成功，而hellodb也未被删除
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bin_log_test       |
| hellodb            |
| inc1               |
| inc2               |
| mysql              |
| performance_schema |
| test               |
| test1              |
| test3              |
+--------------------+

MyCAT实现MySQL读写分离

实验环境：
系统centos7.8 数据库：mariadb-5.6

共三台机器：
mycat  172.16.16.128    mycat 服务器内存建议2G以上
master 172.16.16.88     Mariadb 主服务器
slave  172.16.16.28     Mariadb 从服务器

1）配置主从架构（参考上文）
安装数据库：yum -y install mariadb-server

#master配置
[root@master ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=1
log-bin         #启用二进制日志
general_log=on  #启用通用日志

#slave配置
[root@slave ~]# cat /etc/my.cnf.d/server.cnf 
[mysqld]
server-id=2
log-bin
general_log=on


2）在mycat（172.16.16.128）上安装mycat并启动
[root@mycat ~]#yum -y install java --准备java环境
#确认安装成功
[root@mycat ~]#java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)

#下载并安装
[root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]#mkdir /apps
[root@mycat ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

#配置环境变量
[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]#source /etc/profile.d/mycat.sh

#查看端口
[root@mycat ~]#ss -ntl
State         Recv-Q       Send-Q     Local Address:Port     Peer Address:Port   

LISTEN        0             128              0.0.0.0:22            0.0.0.0:*     

LISTEN        0             128                 [::]:22               [::]:*  

#mycat执行程序类型和使用方式
[root@mycat ~]#file /apps/mycat/bin/mycat 
/apps/mycat/bin/mycat: POSIX shell script, ASCII text executable
[root@mycat ~]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump 
}

#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@mycat ~]#mycat start
Starting Mycat-server...

#可以看到打开多个端口,其中8066端口用于连接MyCAT
[root@mycat ~]#ss -ntlp
State           Recv-Q           Send-Q Local Address:Port   Peer Address:Port   

LISTEN          0                128          0.0.0.0:22          0.0.0.0:*     
		  users:(("sshd",pid=791,fd=5))             
LISTEN          0                1          127.0.0.1:32000       0.0.0.0:*     
		  users:(("java",pid=4640,fd=4))            
LISTEN          0                128             [::]:22             [::]:*     
		  users:(("sshd",pid=791,fd=7))             
LISTEN          0                50                 *:1984             *:*     
		  users:(("java",pid=4640,fd=57))           
LISTEN          0                100               *:8066             *:*     
		  users:(("java",pid=4640,fd=87))           
LISTEN          0                50                 *:43465             *:*     
		  users:(("java",pid=4640,fd=58))           
LISTEN          0                100               *:9066             *:*     
		  users:(("java",pid=4640,fd=83))           
LISTEN          0                50                 *:45259             *:*     
		  users:(("java",pid=4640,fd=56))


#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@mycat ~]#tail /apps/mycat/logs/wrapper.log 
ERROR | wrapper | 2020/02/28 15:21:48 | Startup failed: Timed out waiting for
a signal from the JVM.
ERROR | wrapper | 2020/02/28 15:21:48 | JVM did not exit on request, 
terminated
INFO   | wrapper | 2020/02/28 15:21:48 | JVM exited on its own while waiting to 
kill the application.
STATUS | wrapper | 2020/02/28 15:21:48 | JVM exited in response to signal 
SIGKILL (9).
STATUS | wrapper | 2020/02/28 15:21:52 | Launching a JVM...
INFO   | jvm 2   | 2020/02/28 15:21:52 | OpenJDK 64-Bit Server VM warning: 
ignoring option MaxPermSize=64M; support was removed in 8.0
INFO   | jvm 2   | 2020/02/28 15:22:13 | Wrapper (Version 3.2.3) 
http://wrapper.tanukisoftware.org
INFO   | jvm 2   | 2020/02/28 15:22:13 |   Copyright 1999-2006 Tanuki Software, 
Inc. All Rights Reserved.
INFO   | jvm 2   | 2020/02/28 15:22:13 | 
INFO   | jvm 2   | 2020/02/28 15:22:31 | MyCAT Server startup successfully. see 
logs in logs/mycat.log

#用默认密码123456来连接mycat
[root@mycat ~]#mysql -uroot -p123456 -h 127.0.0.1 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server 
(OpenCloundDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases; #只有一个逻辑数据库
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)

MySQL [TESTDB]> show tables; #首次查询会比较慢，需要多等一会
+------------------+
| Tables in TESTDB |
+------------------+
| address         |
| travelrecord     |
+------------------+
2 rows in set (0.01 sec)

MySQL [TESTDB]> select * from travelrecord ; #未配置读写分离策略，查询失败
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid 
DataSource:0
MySQL [TESTDB]

3）在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@mycat ~]#vim /apps/mycat/conf/server.xml
...省略...
#删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property 
name="processors">32</property> #--> 删除#后面此部分
 .....
<user name="root">  defaultAccount="true">              #连接Mycat的用户名
   <property name="password">passwd</property>          #连接Mycat的密码
   <property name="schemas">TESTDB</property>           #数据库名要和schema.xml相对应
</user>
</mycat:server>
这里使用的是root，密码为passwd,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都
有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。

4）修改schema.xml实现读写分离策略
[root@mycat ~]# cat /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
 </schema>
 <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
 <heartbeat>select user()</heartbeat>
 <writeHost host="host1" url="172.16.16.88:3306" user="root"
   password="passwd">
		 <readHost host="host2" url="172.16.16.28:3306" user="root"
password="passwd" />
 </writeHost>
   </dataHost>
</mycat:schema>

#改完配置后重启mycat
[root@mycat ~]# mycat restart

以上配置中，balance改为1，表示读写分离。最终达到的效果就是172.16.16.88为主库，172.16.16.28为从库
注意：要保证能使用root/passwd权限成功登录172.16.16.88和172.16.16.28机器上面的mysql数据库。
同时，也一定要授权mycat机器能使用root/passwd权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！

5)在后端服务器创建用户并对mycat授权
[root@mycat ~]#mysql -uroot -p
mysql> create database hellodb; #创建hellodb用于测试--与schema.xml中的配置对应
mysql>GRANT ALL ON *.* TO 'root'@'172.16.16.%' IDENTIFIED BY 'passwd' ;
mysql> flush privileges;