Shulou(Shulou.com)06/01 Report--

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.