ADADADADAD

mycat学习01-- mycat我带你入门[ mysql数据库 ]

mysql数据库时间：2024-12-25 09:57:38

作者：文/会员上传

如何提高mysql查询效率

简介：

请耐心读完整篇文章，过程中出现的错误点在文章结尾都有总结和解决办法。服务器架构安装MySQL创建mysql用户groupadd mysqluseradd -r -g mysql -s /bin/false mysql安装MySQL

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

请耐心读完整篇文章，过程中出现的错误点在文章结尾都有总结和解决办法。

服务器架构

安装MySQL

创建mysql用户

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

安装MySQL

yum install -y libaio

cd /usr/local/src/

wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

tar -zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3306

cp -rf mysql-5.7.17-linux-glibc2.5-x86_64 /data/app/mysql-3307

chown -R mysql:mysql /data/app/mysql-3306

chown -R mysql:mysql /data/app/mysql-3307

/data/app/mysql-3306/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data

/data/app/mysql-3307/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data

修改my.cnf

需要修改的参数：

· server-id：保证每个配置文件唯一

· 两台master的自增长ID必须不同

linux-node2

master

cat > /data/app/mysql-3306/my.cnf<<EOF

[client]

port = 3306

socket = /data/app/mysql-3306/mysql.sock

[mysqld]

port = 3306

user = mysql

server-id = 1

bind-address = 0.0.0.0

basedir = /data/app/mysql-3306

datadir = /data/app/mysql-3306/data

socket = /data/app/mysql-3306/mysql.sock

pid-file = /data/app/mysql-3306/mysql.pid

log-error = /data/app/mysql-3306/mysqld.log

skip-name-resolve

log_bin = mysql-bin

log-slave-updates

auto-increment-increment = 2

auto-increment-offset = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3306/my.cnf

slave

cat > /data/app/mysql-3307/my.cnf<<EOF

[client]

port = 3307

socket = /data/app/mysql-3307/mysql.sock

[mysqld]

port = 3307

user = mysql

server-id = 11

bind-address = 0.0.0.0

basedir = /data/app/mysql-3307

datadir = /data/app/mysql-3307/data

socket = /data/app/mysql-3307/mysql.sock

pid-file = /data/app/mysql-3307/mysql.pid

log-error = /data/app/mysql-3307/mysqld.log

skip-name-resolve

log_bin = mysql-bin

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3307/my.cnf

linux-node3

master

cat > /data/app/mysql-3306/my.cnf<<EOF

[client]

port = 3306

socket = /data/app/mysql-3306/mysql.sock

[mysqld]

port = 3306

user = mysql

server-id = 2

bind-address = 0.0.0.0

basedir = /data/app/mysql-3306

datadir = /data/app/mysql-3306/data

socket = /data/app/mysql-3306/mysql.sock

pid-file = /data/app/mysql-3306/mysql.pid

log-error = /data/app/mysql-3306/mysqld.log

skip-name-resolve

log_bin = mysql-bin

log-slave-updates

auto-increment-increment = 2

auto-increment-offset = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3306/my.cnf

slave

cat > /data/app/mysql-3307/my.cnf<<EOF

[client]

port = 3307

socket = /data/app/mysql-3307/mysql.sock

[mysqld]

port = 3307

user = mysql

server-id = 22

bind-address = 0.0.0.0

basedir = /data/app/mysql-3307

datadir = /data/app/mysql-3307/data

socket = /data/app/mysql-3307/mysql.sock

pid-file = /data/app/mysql-3307/mysql.pid

log-error = /data/app/mysql-3307/mysqld.log

skip-name-resolve

log_bin = mysql-bin

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

EOF

chown mysql.mysql /data/app/mysql-3307/my.cnf

启动MySQL

启动服务

linux-node2和linux-node3都执行如下命令

touch /data/app/mysql-3306/mysqld.log && chown mysql.mysql /data/app/mysql-3306/mysqld.log

sed -i 's#/usr/local/mysql#/data/app/mysql-3306#g' /data/app/mysql-3306/bin/mysqld_safe

/data/app/mysql-3306/bin/mysqld_safe --defaults-file=/data/app/mysql-3306/my.cnf --basedir=/data/app/mysql-3306 --datadir=/data/app/mysql-3306/data --user=mysql &

touch /data/app/mysql-3307/mysqld.log && chown mysql.mysql /data/app/mysql-3307/mysqld.log

sed -i 's#/usr/local/mysql#/data/app/mysql-3307#g' /data/app/mysql-3307/bin/mysqld_safe

/data/app/mysql-3307/bin/mysqld_safe --defaults-file=/data/app/mysql-3307/my.cnf --basedir=/data/app/mysql-3307 --datadir=/data/app/mysql-3307/data --user=mysql &

检查端口

ss -lntup |egrep '3306|3307'

tcpLISTEN 0 80 *:3306 *:* users:(("mysqld",19973,22))

tcpLISTEN 0 80 *:3307 *:* users:(("mysqld",20537,22))

配置双主

配置主从

linux-node2

master

cd /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';

Query OK, 0 rows affected (0.05 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 613 | | ||

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

slave

cd /data/app/mysql-3307/

./bin/mysql -uroot -p -S mysql.sock -P 3307

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',

-> MASTER_PORT=3306,

-> MASTER_USER='repl',

-> MASTER_PASSWORD='mysql',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=613;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

linux-node3

master

cd /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> CREATE USER 'repl'@'192.%' IDENTIFIED BY 'mysql';

Query OK, 0 rows affected (0.05 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.%';

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 613 | | ||

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

slave

cd /data/app/mysql-3307/

./bin/mysql -uroot -p -S mysql.sock -P 3307

mysql>

CHANGE MASTER TO MASTER_HOST='192.168.56.13',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='mysql',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=613;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.13

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

配置双主

master的binlog位置

linux-node2 master端

d /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 613 | | ||

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

linux-node3 master端

cd /data/app/mysql-3306/

./bin/mysql -uroot -p -S mysql.sock -P 3306

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 613 | | ||

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

linux-node2 master配置跟linux-node3 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.13',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='mysql',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=613;

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.13

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

linux-node3 master配置跟linux-node2 master同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.12',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='mysql',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=613;

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node3-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

验证

从linux-node2上验证

在linux-node2 master上创建数据

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

mysql> use test;

Database changed

mysql> create table temp(id int,name varchar(64));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into temp values(1,'aaa');

Query OK, 1 row affected (0.28 sec)

mysql> CREATE TABLE temp2(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT ,nname VARCHAR(64));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp2(nname) values('bbb');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test.temp;

+------+------+

| id| name |

+------+------+

|1 | aaa |

+------+------+

1 row in set (0.01 sec)

在linux-node2 slave上查看数据

mysql> select * from test.temp;

+------+------+

| id| name |

+------+------+

|1 | aaa |

+------+------+

1 row in set (0.00 sec)

在linux-node3 master上查看数据

mysql> select * from test.temp;

+------+------+

| id| name |

+------+------+

|1 | aaa |

+------+------+

1 row in set (0.00 sec)

在linux-node3 slave上查看数据

mysql> select * from test.temp;

+------+------+

| id| name |

+------+------+

|1 | aaa |

+------+------+

1 row in set (0.00 sec)

从linux-node3上验证

在linux-node3 master上创建数据

mysql> use test;

mysql> insert into temp2(nname) values('ddd');

Query OK, 1 row affected (0.02 sec)

mysql> insert into temp2(nname) values('fff');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

+----+-------+

在linux-node3 slave上查看数据

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

+----+-------+

3 rows in set (0.00 sec)

在linux-node2 master上查看数据

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

+----+-------+

3 rows in set (0.00 sec)

在linux-node2 slave上查看数据

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

+----+-------+

3 rows in set (0.00 sec)

结论

· 在任意一个master端更新数据，其他任意端都可以更新数据

· 两台服务器配置了间隔自增长，数据不同冲突

linux-node1上安装mycat

安装mycat

cd /usr/local/src

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

mv mycat /data/app/mycat-1.6

ln -s /data/app/mycat-1.6 /data/app/mycat

修改schema.xml

· balance="1"
全部的readHost与stand by writeHost参与select语句的负载均衡

· writeType="0"
所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties

· switchType="1"
默认值为1，自动切换

cd /data/app/mycat

cp conf/schema.xml conf/schema.xml.bak

cat > conf/schema.xml <<EOF

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>

<dataNode name="dn1" dataHost="node1" database="test" />

<dataHost name="node1" maxCon="10" minCon="5" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">

<heartbeat>select user()</heartbeat>

<writeHost host="master1" url="192.168.56.12:3306" user="root" password="mysql">

<readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />

</writeHost>

<writeHost host="master2" url="192.168.56.13:3306" user="root" password="mysql">

<readHost host="slave2" url="192.168.56.13:3307" user="root" password="mysql" />

</writeHost>

</dataHost>

</mycat:schema>

EOF

启动mycat

./bin/mycat start

ss -lntup |egrep '(8066|9066)'

tcpLISTEN 0 100:::8066 :::* users:(("java",16546,79))

tcpLISTEN 0 100:::9066 :::* users:(("java",16546,75))

验证mycat服务是否正常

在linux-node2-master端配置mycat连接账号

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'192.%' IDENTIFIED BY 'mysql';

Query OK, 0 rows affected, 1 warning (0.07 sec

在mycat服务器上安装mysql服务，但是不启动

步骤省略，详细内容可以参考上面的MySQL安装

使用mysql的客户端连接mycat

cd /data/app/mysql/

./bin/mysql -uroot -p -P 8066 -h 192.168.56.11 ##连接mycat，初始密码123456

mysql> show databases;

+----------+

| DATABASE |

+----------+

| TESTDB|

+----------+

1 row in set (0.00 sec)

mysql> use TESTDB;

mysql> insert into temp2(nname) values('eee');

Query OK, 1 row affected (0.09 sec)

mysql> insert into temp2(nname) values('ggg');

Query OK, 1 row affected (0.01 sec)

linux-node3 slave端查看数据是否同步

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

| 5 | eee|

| 7 | ggg|

+----+-------+

5 rows in set (0.00 sec)

结果发现数据写入到了linux-node2 slave端

测试

服务自动迁移

关闭linux-node2 master的MySQL服务

mysql> shutdown;

Query OK, 0 rows affected (0.01 sec)

shell > ss -lntup |grep 3306

mycat端插入新的数据查看数据是否同步

mysql> insert into temp2(nname) values('mmmm');

Query OK, 1 row affected (0.07 sec)

mysql> insert into temp2(nname) values('nnnn');

Query OK, 1 row affected (0.01 sec)

linux-node3 slave端查看数据是否同步

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

| 5 | eee|

| 7 | ggg|

| 8 | mmmm |

| 10 | nnnn |

+----+-------+

7 rows in set (0.00 sec)

linux-node2 slave端查看数据是否同步

mysql> select * from test.temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

| 5 | eee|

| 7 | ggg|

+----+-------+

5 rows in set (0.00 sec)

发现因为linux-node2的master端已经挂了，数据不能同步了

数据访问是否正常

登录到mycat服务器上执行如下命令：

mysql> select * from temp2;

+----+-------+

| id | nname |

+----+-------+

| 1 | bbb|

| 2 | ddd|

| 4 | fff|

| 5 | eee|

| 7 | ggg|

| 8 | mmmm |

| 10 | nnnn |

+----+-------+

7 rows in set (0.00 sec)

执行多次发现结果一样，说明在一台master端挂掉的情况下，其连接的slave端也被剔除，因此数据完整性可以保证

故障汇总

第一次配置的时候maser端没有配置log-slave-updates，导致node3-slave上没有node2-master端的数据。
解释：
从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。

解决办法：

[mysqld]

log-slave-updates

展开阅读全文 ∨

mycat学习01-- mycat我带你入门.docx

将本文的Word文档下载到电脑

推荐度：

下载

热门标签: mycat入门01

申请https需要什么条件
11-20

php怎么实现https请求
11-19

https地址无法登陆怎么办
11-20

为什么无法访问https
11-20

小程序为什么要用https
11-20

https解密是在什么层
11-19

java如何实现https访问
11-20

服务器如何配置多个https域名
11-20

什么是https劫持
11-19

java后台https如何写
11-20

https异常怎么修复
11-19

php如何访问https
11-19

虚拟机怎么使用https
11-19

幻兽帕鲁孵化器制作在哪里
11-19

南昌高端网站建设方案有哪些
11-19

PHP的php.ini文件有什么作用
11-19

精品

申请https需要什么条件

HBase借助Prometheus怎么优化12-25

Prometheus下HBase报警如何设置12-25

Prometheus与HBase集成难不难12-25

Prometheus监控HBase数据来源12-25

怎样确保Prometheus监控HBase12-25

Prometheus能否全面监控HBase12-25

HBase在Prometheus中如何展示12-25

Prometheus监控HBase有何限制12-25

热门推荐

hadoop分布式数据库怎样管理
2024-12-03

ajax和数据库交互的方法是什么
2024-12-03

hadoop archive适合哪些应用场景
2024-12-06

hbase metadata能否备份恢复
2024-12-25

如何使用Kafka构建可扩展的日志收集系统
2024-12-03

大家都在看

淘宝积分怎么兑换东西，淘宝积分如何使用

淘宝账号怎么换绑支付宝账号，淘宝怎么更改支付宝账号

淘宝工厂直营店怎么联系客服，淘宝直营店怎么联系客服

淘宝付款方式怎么设置在哪，淘宝如何切换付款方式

淘宝收货人姓名要真实的吗，快递收件人可以不写真名吗

淘宝规则2023，淘宝发货规则

怎么关闭淘宝芝麻GO，淘宝芝麻信用怎么关闭

淘宝评价隐藏怎么恢复，淘宝评论被自己隐藏了怎么办

天猫超市半日达是快递还是外卖，天猫超市半日达是从哪发货

淘宝名称可以改么？淘宝名可以改吗在哪里改

淘宝消费者投诉客服热线，怎么找淘宝平台的客服

京东无货订购怎么取消，京东app如何取消无货订购

哪个平台购物是正品，什么平台买的品牌是正品

京东白条激活没事吧，京东白条激活了有啥影响么

淘宝特价版扣点费用，淘宝特价版扣点多少