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
.......
.......
.......
[root@mycat conf]# more schema.xml
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> password="123456">
[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>