MySQL编译安装及多实例安装

MySQL安装

编译安装可安装需要的版本，特别是主从复制时，需要版本一致

MySQL5.7编译安装

# 删除默认的mariadb-libs
rpm -e --nodeps mariadb-libs
rm -f /etc/my.cnf

wget https://soft.vpser.net/datebase/mysql/mysql-5.7.30.tar.gz

yum -y install make cmake gcc gcc-c++ gcc-g77 flex bison wget zlib zlib-devel openssl openssl-devel ncurses ncurses-devel libaio-devel rpcgen libtirpc-devel patch cyrus-sasl-devel

tar xf mysql-5.7.30.tar.gz && cd mysql-5.7.30

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_BOOST=/root/mysql-5.7.30/boost

# 需要1G内存，“make -j 4”需要更多的内存
make
make install
groupadd mysql
useradd -s /sbin/nologin -M -g mysql mysql
# 编辑配置文件，见下文
mkdir /usr/local/mysql/var && chown mysql.mysql /usr/local/mysql/var

/usr/local/mysql/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql
#5.6

chgrp -R mysql /usr/local/mysql/.
\cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
cat > /etc/ld.so.conf.d/mysql.conf<<EOF
/usr/local/mysql/lib
/usr/local/lib
EOF
ldconfig
ln -sf /usr/local/mysql/lib/mysql /usr/lib/mysql
ln -sf /usr/local/mysql/include/mysql /usr/include/mysql

ln -sf /usr/local/mysql/bin/mysql /usr/bin/mysql
ln -sf /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump
ln -sf /usr/local/mysql/bin/myisamchk /usr/bin/myisamchk
ln -sf /usr/local/mysql/bin/mysqld_safe /usr/bin/mysqld_safe
ln -sf /usr/local/mysql/bin/mysqlcheck /usr/bin/mysqlcheck

chkconfig mysql on
/etc/init.d/mysql start
# 默认密码为空
mysql -uroot -p
# 这版本无test数据库，root用户只有一个，没有匿名用户，如果不是这样需要清理
#update user set authentication_string=password('root') where user='root';
#select user,host,authentication_string from user;
#flush privileges;
#禁止非192.168.1.5访问
#iptables -A INPUT ! -s 192.168.1.5 -p tcp --dport 3306 -j DROP

配置文件：

其中的一些参数需要根据内存大小修改。

cat > /etc/my.cnf<<EOF
[client]
port        = 3306
socket      = /tmp/mysql.sock
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M
performance_schema_max_table_instances = 500
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

#禁用插件keyring_file
early-plugin-load = ""
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 16M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer_size = 2M
write_buffer_size = 2M
[mysqlhotcopy]
interactive-timeout
EOF

MySQL8编译安装

需要内存大于1G，8.0.20需要高版本的gcc和cmake

如果没有特殊需求不建议使用MySQL8，功能多，源码大（240+MB），性能也不是特别高

# 安装依赖
yum -y install make cmake gcc gcc-c++ gcc-g77 flex bison wget zlib zlib-devel openssl openssl-devel ncurses ncurses-devel libaio-devel rpcgen libtirpc-devel patch cyrus-sasl-devel

# 编译安装cmake
wget https://cmake.org/files/v3.9/cmake-3.9.2.tar.gz
tar xf cmake-3.9.2.tar.gz && cd cmake-3.9.2
./bootstrap && make && make install
/usr/local/bin/cmake --version

# 配置gcc
yum -y install centos-release-scl
yum -y install devtoolset-8
#重新登录
scl enable devtoolset-8 bash
gcc -v

wget https://soft.vpser.net/datebase/mysql/mysql-8.0.20.tar.gz
tar xf mysql-8.0.20.tar.gz && cd mysql-8.0.20
mkdir build && cd build
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_BOOST=/root/mysql-5.8.20/boost

make && make install
mkdir /usr/local/mysql/data && chown mysql.mysql /usr/local/mysql/data

/usr/local/mysql/bin/mysqld --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
\cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chgrp -R mysql /usr/local/mysql/.

#my.cnf中修改默认密码插件，expire_logs_days已更名
#default_authentication_plugin = mysql_native_password
#binlog_expire_logs_seconds = 864000

#修改root密码
#set password for root@localhost = 'root';

#33060端口，禁用x协议
#mysqlx=0

多实例安装

yum -y install autoconf
cd mysql-5.6.40
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56 -DSYSCONFDIR=/data/mysql/ -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1

make && make install
groupadd mysql
useradd -s /sbin/nologin -M -g mysql mysql
mkdir -p /data/mysql/3306/data
chown -R mysql:mysql /data/mysql/3306/data
#准备配置文件/data/mysql/3306/my.cnf，略，port:3306
/usr/local/mysql56/scripts/mysql_install_db --defaults-file=/data/mysql/3306/my.cnf --basedir=/usr/local/mysql56 --datadir=/data/mysql/3306/data --user=mysql
chgrp -R mysql /usr/local/mysql56/.

tar xf boost_1_59_0.tar.bz2
cd mysql-5.7.22
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_BOOST=/root/boost_1_59_0

make && make install

mkdir -p /data/mysql/3307/data
chown -R mysql.mysql /data/mysql/3307
#准备配置文件/data/mysql/3307/my.cnf，略，port:3307
/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf --initialize-insecure --basedir=/usr/local/mysql57 --user=mysql
chgrp -R mysql /usr/local/mysql57/.

ln -sf /usr/local/mysql56/bin/mysql /usr/bin/mysql56
ln -sf /usr/local/mysql57/bin/mysql /usr/bin/mysql57
ln -sf /usr/local/mysql57/bin/mysqladmin /usr/bin/mysqladmin57
ln -sf /usr/local/mysql56/bin/mysqladmin /usr/bin/mysqladmin56
ln -sf /usr/local/mysql56/bin/mysqld_safe /usr/bin/mysqld_safe56
ln -sf /usr/local/mysql57/bin/mysqld_safe /usr/bin/mysqld_safe57

# 开启服务
mysqld_safe57 --defaults-file=/data/mysql/3307/my.cnf &>/dev/null &
mysqld_safe56 --defaults-file=/data/mysql/3306/my.cnf &>/dev/null &

# 登录MySQL
mysql57 -A -uroot -p -S /data/mysql/3307/mysql.sock
# 关闭服务
mysqladmin57 -uroot -p -S /data/mysql/3307/mysql.sock shutdown