Mysql master-slave mycat installation Concise tutorial
I. basic installation of database
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
# configure completion variable #
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. Start mysql
# service mysql start
8. Log in to mysql, change password and configure remote access
# Log in to mysql with an empty password
# mysql-u root-p
# allow remote access for root users
Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY' your_password' WITH GRANT OPTION
# Refresh permissions
Mysql > FLUSH PRIVILEGES
Mysql > exit
2. Configure master-slave
1. Parameter difference
Server_id and server_uuid are different.
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 establishes replication users
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 sets the replication start position
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
Establishing Test Library by 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 >
Third, install mycat
1. Install Java
Hint: normal mycat should be independent of a machine.
A total of 3 hosts
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 ~] #
two。 Install mycat
# cd / opt
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
Official website address: 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 login proc
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
.
.
.
Mycat123456
TESTDB
[root@mycat conf] # more schema.xml
Select user ()
[root@mycat conf] #
# vi / etc/profile
Export MYCAT_HOME=/opt/mycat
Export PATH=$MYCAT_HOME/bin:$PATH:$JAVA_HOME/bin
4. Install MySQL softwar
# 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. Start mycat
[root@masterdb ~] # mycat start
Starting Mycat-server...
[root@masterdb ~] #
6. Test use
[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), (2)
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.01sec)
Mysql >
