一、数据库基本安装

1.down mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz

2.install

# yum install -y perl perl-Data-Dumper libaio

# mv mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz /usr/local/

# cd /usr/local/

# tar zxvf mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz

# mv mysql-5.6.37-linux-glibc2.12-x86_64 mysql-5.6.37

# rm -rf mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz

# groupadd mysql

# useradd -r -g mysql mysql

# chown mysql.mysql -R mysql-5.6.37/

# mkdir /home/mysql

# chown mysql.mysql /home/mysql/

3.configure conf

# cd /usr/local/mysql-5.6.37/

# cp support-files/my-default.cnf /etc/my.cnf

cp: overwrite ‘/etc/my.cnf’? y

4.boot configure

# cp support-files/mysql.server /etc/init.d/mysql

# chmod +x /etc/init.d/mysql

# chkconfig --add mysql

#

# vi /etc/init.d/mysql

### 配置补全变量 ###

basedir=/usr/local/mysql-5.6.37

datadir=/usr/local/mysql-5.6.37/data

5.env setting

# vim /etc/profile

export MYSQL_HOME=/usr/local/mysql-5.6.37

export PATH=$PATH:$MYSQL_HOME/bin

6. configure file

# vi /etc/my.cnf

[mysqld]

character_set_server = utf8

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# GENERAL

datadir = /usr/local/mysql-5.6.37/data

socket = /usr/local/mysql-5.6.37/mysql.sock

pid_file = /usr/local/mysql-5.6.37/mysql.pid

user = mysql

port = 3306

bind_address = 0.0.0.0

# INNODB

default-storage-engine = InnoDB

innodb_file_per_table = 1

innodb_buffer_pool_size = 800MB

innodb_log_file_size = 256MB

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2

sync_binlog = 20

# MyISAM

myisam_recover=default

key_buffer_size = 200MB

# LOGGING

log_error = /usr/local/mysql-5.6.37/mysql-error.log

slow_query_log = 1

long_query_time = 0.5

slow_query_log_file = /usr/local/mysql-5.6.37/mysql-slow.log

# BINLOG

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 30

# MASTER

# server-id=1

# SLAVE

# server_id = 2

# relay_log = mysql-relay-bin

# log_slave_updates = 1

# read_only = 1

# OTHER

skip_name_resolve

max_connect_errors = 5000

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_type = 0

query_cache_size = 0

max_connections = 5000

thread_cache_size = 64

open_files_limit = 65535

max_allowed_packet = 64M

[client]

default_character_set=utf8

socket = /usr/local/mysql-5.6.37/mysql.sock

port = 3306

7.init db

# su - mysql

Last login: Tue Sep 5 14:26:36 CST 2017 on pts/0

-bash-4.2$ cd /usr/local/mysql-5.6.37

-bash-4.2$ scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql-5.6.37 --datadir=/usr/local/mysql-5.6.37/data

-bash-4.2$ exit

logout

9.启动mysql

# service mysql start

8.登录mysql及改密码与配置远程访问

#登录mysql,密码为空

# mysql -u root -p

#允许root用户远程访问

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

#刷新权限

mysql>FLUSH PRIVILEGES;

mysql>exit

二、配置master-slave

1.参数区别

server_id、server_uuid均不同

master

[mysqld]

log-bin=mysql-bin

server-id=1

slave (/etc/my.cnf)

[mysqld]

log_bin = mysql-bin

server_id = 2

relay_log = mysql-relay-bin

log_slave_updates = 1

read_only = 1

1).master

mysql> show variables like 'server%';

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

| Variable_name | Value|

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

| server_id | 1|

| server_id_bits | 32|

| server_uuid| 4875d4d5-9211-11e7-90ac-000c29e56ccc |

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

3 rows in set (0.04 sec)

mysql>

mysql>

2).salve

mysql> show variables like 'server%';

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

| Variable_name | Value|

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

| server_id | 2|

| server_id_bits | 32|

| server_uuid| eb40bb6a-920d-11e7-9096-000c29e0d6fa |

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

3 rows in set (0.04 sec)

mysql>

2.master建立复制用户

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 | 120 | | ||

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

1 row in set (0.00 sec)

mysql> grant replication slave,replication client on *.* to 'replic_user'@'172.16.3.%' identified by 'repl123456';

Query OK, 0 rows affected (0.28 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.29 sec)

mysql>

3.slave设置复制起始位置

mysql> CHANGE MASTER TO MASTER_HOST='172.16.3.226',

-> MASTER_PORT=3306,

-> MASTER_USER='replic_user',

-> MASTER_PASSWORD='repl123456',

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

-> MASTER_LOG_POS=120;

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

mysql> start slave;

Query OK, 0 rows affected (0.31 sec)

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.3.226

Master_User: replic_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 433

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 596

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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: 433

Relay_Log_Space: 769

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: 4875d4d5-9211-11e7-90ac-000c29e56ccc

Master_Info_File: /usr/local/mysql-5.6.37/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave 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)

ERROR:

No query specified

master建立测试库

mysql> create database db1;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.3.%' IDENTIFIED BY '123456' WITH GRANT OPTION;

Query OK, 0 rows affected (0.29 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql>

mysql>

三、安装mycat

1.安装Java

提示：mycat正常应该独立一台机器

共3台主机

master（172.16.3.226）,

slave（172.16.3.228）,

mycat（172.16.3.229）

jdk-8u144-linux-x64.tar.gz

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

# cd /opt/

# tar zxvf jdk-8u144-linux-x64.tar.gz

# rm -rf jdk-8u144-linux-x64.tar.gz

# vi /etc/profile

### normal add stand alone ###

export JAVA_HOME=/opt/jdk1.8.0_144

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

export PATH=$PATH:$JAVA_HOME/bin

[root@masterdb ~]# java -version

java version "1.8.0_144"

Java(TM) SE Runtime Environment (build 1.8.0_144-b01)

Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)

[root@masterdb ~]#

2.安装mycat

# cd /opt

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

官网地址：http://dl.mycat.io/1.6-RELEASE/

# tar -xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

# rm -rf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

# groupadd mycat

# useradd -g mycat mycat

# chown -R mycat:mycat mycat/

3.mycat 登录流程

firewall--> user (logical user)--> schemas(logical db) -->

dataNode(physical db/logical host) --> dataHost(logical host)

-->writeHost or readHost(physical host)

# cd /opt/mycat/conf/

# vi server.xml

.......

.......

.......

</system>

<!-- 全局SQL防火墙设置 -->

<!--

<firewall>

<whitehost>

<host host="172.16.3.229" user="mycat"/>

</whitehost>

<blacklist check="false">

</blacklist>

</firewall>

-->

<user name="mycat">

<property name="password">mycat123456</property>

<property name="schemas">TESTDB</property>

</user>

</mycat:server>

[root@mycat conf]# more 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="db1" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost host="hostM1" url="172.16.3.226:3306" user="root"

password="123456">

<!-- can have multi read hosts -->

<readHost host="hostS1" url="172.16.3.228:3306" user="root" password="123456" />

</writeHost>

</dataHost>

</mycat:schema>

[root@mycat conf]#

# vi /etc/profile

export MYCAT_HOME=/opt/mycat

export PATH=$MYCAT_HOME/bin:$PATH:$JAVA_HOME/bin

4.安装MySQL软件

# cd /opt

# tar zxvf mysql-5.6.37-linux-glibc2.12-x86_64.tar.gz

# mv mysql-5.6.37-linux-glibc2.12-x86_64 mysql-5.6.37

# vi /etc/profile

export JAVA_HOME=/opt/jdk1.8.0_144

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

export MYCAT_HOME=/opt/mycat

export MYSQL_HOME=/opt/mysql-5.6.37

export PATH=$MYCAT_HOME/bin:$PATH:$JAVA_HOME/bin:$MYSQL_HOME/bin

5.启动mycat

[root@masterdb ~]# mycat start

Starting Mycat-server...

[root@masterdb ~]#

6.测试使用

[root@mycat conf]# mysql -u mycat -h 172.16.3.229 -P 8066 -pmycat123456 -D TESTDB

Warning: Using a password on the command line interface can be insecure.

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2017, 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> select * from t1;

Empty set (0.28 sec)

mysql> insert into t1 values(1,'Peter'),(2,'Chris');

Query OK, 2 rows affected (0.06 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t1;

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

| id| name |

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

|1 | Peter |

|2 | Chris |

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

2 rows in set (0.01 sec)

mysql>