Cmake compiles and installs MySQL & mysqld_multi to deploy MySQL multi-instance solution

mysqld_multi deployment MySQL multiple instances: using separate configuration files to achieve multiple instances, this way of customizing the configuration of each instance is not very good, the advantage is that it is easy to manage, centralized management
Multiple configuration files are recommended. Good in practical application, weak coupling, easy to configure, especially when master-slave replication

Distinction between MySQL Binary Packets and Source Packets

• Binary formats have long package names with version numbers, adaptive platforms, adaptive hardware types, and so on.
  mysql-5.0.45.tar.gz is the source package (compile and install)
• The source format is just a tar package with a version number.
  mysql-5.0.45-linux-x86_64-glibc23.tar.gz is a binary package

All operations depend on the actual situation.

• Camek Download: https://cmake.org/download/
• MySQL Download: https://dev.mysql.com/downloads/mysql/

1.1 Preparing installation environment

1. First check if mysql has been installed:

> rpm -qa | grep mysql

2. In some cases, uninstall the mysql installed before:

> rpm-e -- nodeps XXX (xxx is the search result)

3. Delete all relevant documents:

> rm -f /etc/my.cnf

1.2 Download the cmake installation package, compile and install cmake

1. Download and unzip Cmake

> wget  https://cmake.org/files/v3.9/cmake-3.9.1.tar.gz
> tar zxf  cmake-3.9.1.tar.gz

2. Compile and install cmake

> cd cmake-3.9.1
> ./configure
> gmake
> echo $?      #Compile but determine if there is a compilation error before installing
> gmake install

1.3 Download MySQL Compile and Install MySQL

1. Install dependency packages

> yum -y install libaio libaio-devel     
> yum -y install ncurses-devel         

2. Creating User Group Users

> groupadd mysql
> useradd mysql -s /sbin/nologin -M -g mysql

3. Download and decompress

> wget https://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.57.tar.gz
> tar -zxf mysql-5.5.57.tar.gz

4.cmake compile and install (compile time cannot specify configuration file port number mysql.sock address, etc.)

> cd mysql-5.5.57
> cmake \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.5.57 -DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc/my.cnf  -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all
> make && make install

5. Check if the installation is successful

> echo $?
> 0
#(Output 0 indicates success)

6. Setting Soft Links and Configuration Files

> ln -s /usr/local/mysql55 /usr/local/mysql 

1.4 Configure MySQL Multiple Instances (mysqld_multi mode)

1. Create a multi-instance data catalog

> mkdir -pv /data/mysql/{3306,3307}

2. Setting Access Permissions

> chown -R mysql:mysql /data/mysql

3. Initialization of database

> cd /usr/local/mysql/scripts/
> ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/3306 --user=mysql
> ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/3307 --user=mysql

4. Modify the configuration file (note the path and port number)

[mysqld_multi] 
mysqld=/usr/local/mysql/bin/mysqld_safe 
mysqladmin=/usr/local/mysql/bin/mysqladmin 
user=multi_admin 
password=multi_password 
[mysqld3306] 
socket=/tmp/mysql3306.sock 
port=3306 
pid-file=/data/mysql/data3306/hostname3306.pid 
datadir=/data/mysql/data3306 
user=mysql 
server-id=3306 
[mysqld3307] 
socket=/tmp/mysql3307.sock 
port=3307 
pid-file=/data/mysql/data3307/hostname3307.pid 
datadir=/data/mysql/data3307 
user=mysql 
server-id=3307

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe 
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld1]
port = 3306
socket = /tmp/3306.sock
pid-file=/data/mysql/3306/3306.pid
datadir=/data/mysql/3306
user=mysql
server-id=3306
log-slow-queries=slow_query.txt
long_query_time=2
skip-external-locking
skip-name-resolve
skip-innodb
max_allowed_packet = 256M
query_cache_size=256M
max_connections=2000
max_connect_errors=10000
key_buffer_size=6000M
read_buffer_size=32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size=512M
tmp_table_size=1024M
old-passwords
interactive_timeout=60
wait_timeout=60
connect_timeout=60
table_cache=8192
thread_cache_size=512
sort_buffer_size=128M
back_log = 500
thread_concurrency=48
expire_logs_days=10
log-bin=mysql-bin

[mysqld2]
port = 3307
socket = /tmp/3307.sock
pid-file=/data/mysql/3307/3307.pid
datadir=/data/mysql/3307
user=mysql
server-id=3307
log-slow-queries=slow_query.txt
long_query_time=2
skip-external-locking
skip-name-resolve
skip-innodb
max_allowed_packet = 256M
query_cache_size=256M
max_connections=2000
max_connect_errors=10000
key_buffer_size=6000M
read_buffer_size=32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size=512M
tmp_table_size=1024M
old-passwords
interactive_timeout=60
wait_timeout=60
connect_timeout=60
table_cache=8192
thread_cache_size=512
sort_buffer_size=128M
back_log = 500
thread_concurrency=48
expire_logs_days=10
log-bin=mysql-bin

[mysqldump]
quick
max_allowed_packet = 512M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

5. Managing multiple instances of MySQL through the tool mysqld_multi under bin

#start-up
    > /usr/local/mysql/bin/mysqld_multi start
    > /usr/local/mysql/bin/mysqld_multi start 1-2
    > /usr/local/mysql/bin/mysqld_multi start 1
#Viewable running status
    > /usr/local/mysql/bin/mysqld_multi report
#Close
    > /usr/local/mysql/bin/mysqld_multi stop
    > /usr/local/mysql/bin/mysqld_multi stop 1-2
    > /usr/local/mysql/bin/mysqld_multi stop 1

6. Check if the startup is successful

    > ps -ef | grep mysqld
#perhaps
    > netstat -anp | grep 3306

7. Connecting to the database

1. Specify the ip port number to enter
    > mysql -u root -h 127.0.0.1 -P 3306
 2. Specify socket login, suitable for local connection
    > mysql -u root -p -S /tmp/3306.sock
 3. Enter the password.

8. Initialize password and authorize remote login

mysqladmin username and password need to be consistent with / ect/my.cnf, otherwise stop will not serve (every instance must be executed)

    > /usr/local/mysql/bin/mysqladmin -u root password "yourpassword" -S /tmp/3306.sock
    > /usr/local/mysql/bin/mysql -uroot -p -S /tmp/3306.sock
#Enter password
    mysql> grant all privileges on *.* to 'root'@'%' identified by '3306password' with grant option;
    mysql> flush privileges;