mysql master-slave mycat 安装简明教程

一、数据库基本安装

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>