MySQL master-slave mycat Installation Concise tutorial

First, the basic installation of the 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

# # # Configuration complement 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

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 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 and change password and configure remote access

#登录mysql, the password is empty

# mysql-u Root-p

#允许root用户远程访问

Mysql>grant all privileges on * * to ' root ' @ '% ' identified by ' Your_password ' with GRANT OPTION;

#刷新权限

Mysql>flush privileges;

Mysql>exit

Second, the configuration Master-slave

1. Parameter differences

SERVER_ID, 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 Creating a replication user

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 Set Replication Start location

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 have 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 Build Test Library

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, installation Mycat

1. Installing Java

Tip: Install on master, should be a separate machine normally

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

[Email protected] ~]# 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)

[Email protected] ~]#

2. Installing Mycat

# cd/opt

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

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 Process

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>

<!--global SQL firewall Settings--

<!--

<firewall>

<whitehost>

</whitehost>

<blacklist check= "false" >

</blacklist>

</firewall>

-

<user name= "Mycat" >

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

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

</user>

</mycat:server>

[Email protected] 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= "datanode=" "DN1" >

</schema>

<datanode name= "dn1" datahost= "Localhost1" database= "DB1"/>

<datahost name= "Localhost1" maxcon= "+" mincon= "balance=" 1 "

Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">"

<!--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>

[Email protected] conf]#

# Vi/etc/profile

Export Mycat_home=/opt/mycat

Export path= $MYCAT _home/bin: $PATH: $JAVA _home/bin

4. Installing the MySQL Software

# 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

[[email protected] ~]# Mycat start

Starting mycat-server ...

[Email protected] ~]#

6. Test use

[Email protected] conf]# mysql-u mycat-h 172.16.3.229-p 8066-pmycat123456-d TESTDB

Warning:using a password on the command line interface can is insecure.

Reading table information for completion of table and column names

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), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names 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>

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1962913

MySQL master-slave mycat Installation Concise tutorial