mysql master-slave mycat 安装简明教程

423212

一、数据库基本安装

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  
server_id = 1
# 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
# 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

[mysqldump]  
quick
max_allowed_packet = 16M
[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

提示：安装在master上，正常应该独立一台机器

jdk-8u144-linux-x64.tar.gz
http://www.oracle.com/technetwor ... nloads-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


[iyunv@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)
[iyunv@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>

[iyunv@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>
[iyunv@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
[iyunv@masterdb ~]#  mycat start
Starting Mycat-server...
[iyunv@masterdb ~]#

6.测试使用

[iyunv@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>