yum && 编译 安装mysql 5.7 多实例
yum安装
[root@localhost ~]# wget http://repo.mysql.com/mysql57-community-release-el7.rpm [root@localhost ~]# rpm -ivh mysql57-community-release-el7.rpm [root@localhost ~]# yum install mysql mysql-server
用科技大学的mysql源下载会更快些
[root@localhost ~]# rpm -ivh http://mirrors.ustc.edu.cn/mysql-repo/mysql57-community-release-el7.rpm
初始化
[root@localhost ~]# grep "password" /var/log/mysqld.log 2017-04-12T00:45:32.972626Z 1 [Note] A temporary password is generated for root@localhost: kvyqpEMl<9b0 #随机密码 [root@localhost ~]# mysqladmin -u root -p password #重置密码 Enter password: New password: Confirm new password: [root@localhost ~]# systemctl start mysqld [root@localhost ~]# systemctl enable mysqld
编译安装
环境准备
[root@localhost ~]yum install -y gcc gcc-c++ openssl openssl-devel ncurses ncurses-devel cmake bison* [root@localhost ~]# mkdir /data/ [root@localhost ~]# mkdir /data/mysql/ [root@localhost ~]# mkdir /data/mysql/data/ [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -g mysql -s /sbin/nologin mysql [root@localhost ~]# cd /usr/local/src/ [root@localhost ~]# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.18.tar.gz [root@localhost src]# tar zvxf mysql-boost-5.7.18.tar.gz [root@localhost src]# cd mysql-5.7.18
编译安装
cmake . \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/data/mysql/data \ -DSYSCONFDIR=/etc \ -DWITH_BOOST=boost \ -DEFAULT_CHARSET=utf8\ -DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=all \ -DMYSQL_UNIX_ADDR=/data/mysql/data/mysqld.sock \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DMYSQL_TCP_PORT=3306
# -DCMAKE_INSTALL_PREFIX=/usr/local/mysql #安装路径 # -DMYSQL_DATADIR=/data/mysql/data #数据文件存放位置 # -DSYSCONFDIR=/etc #my.cnf路径 # -DWITH_BOOST=boost #指定boost库 # -DDEFAULT_CHARSET=utf8 #默认字符 # -DDEFAULT_COLLATION=utf8_general_ci #默认排序规则 # -DEXTRA_CHARSETS=all #安装所有的字符集 # -DMYSQL_UNIX_ADDR=/data/mysql/data/mysqld.sock #连接数据库socket路径 # -DENABLED_LOCAL_INFILE=1 #允许从本地导入数据 # -DWITH_MYISAM_STORAGE_ENGINE=1 #支持MyIASM引擎 # -DWITH_INNOBASE_STORAGE_ENGINE=1 #支持InnoDB引擎 # -DWITH_MEMORY_STORAGE_ENGINE=1 #支持Memory引擎 # -DWITH_PARTITION_STORAGE_ENGINE=1 #安装支持数据库分区 # -DWITH_READLINE=1 #快捷键功能 # -DMYSQL_TCP_PORT=3306 #端口 [root@localhost mysql-5.7.18]# make -j `grep processor /proc/cpuinfo | wc -l` && make install # grep processor /proc/cpuinfo 根据cpu核心数指定编译时的线程数(多核cpu会快很多)
初始化配置
[root@localhost ~]# chown -R mysql:mysql /data/mysql/data/ [root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysqld --initialize --user=mysql 2017-04-16T09:35:15.850060Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-04-16T09:35:16.331637Z 0 [Warning] InnoDB: New log files created, LSN=45790 2017-04-16T09:35:16.408336Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2017-04-16T09:35:16.467944Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ffbe4813-2287-11e7-9d8c-000c298c8776. 2017-04-16T09:35:16.469737Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2017-04-16T09:35:16.470947Z 1 [Note] A temporary password is generated for root@localhost: sRXga&j#w5n [root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql
配置文件
[root@localhost ~]# cp /etc/my.cnf /etc/my.cnf.bak [root@localhost ~]# rm -rf /etc/my.cnf [root@localhost ~]# vim /etc/my.cnf [client] port = 3306 socket = /data/mysql/data/mysql.sock default-character-set = utf8 [mysqld] port = 3306 socket = /data/mysql/data/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql/data pid-file = /data/mysql/data/mysql.pid user = mysql bind-address = 0.0.0.0 server-id = 1 init-connect = 'SET NAMES utf8' character-set-server = utf8 #skip-name-resolve #skip-networking back_log = 300 max_connections = 1000 max_connect_errors = 6000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M key_buffer_size = 4M thread_cache_size = 8 query_cache_type = 1 query_cache_size = 8M query_cache_limit = 2M ft_min_word_len = 4 log_bin = mysql-bin binlog_format = mixed expire_logs_days = 30 log_error = /data/mysql/data/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/data/mysql-slow.log performance_schema = 0 explicit_defaults_for_timestamp #lower_case_table_names = 1 skip-external-locking default_storage_engine = InnoDB #default-storage-engine = MyISAM innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M EOF
系统服务
[root@localhost ~]# cp /usr/local/src/mysql-5.7.18/support-files/mysql.server /etc/init.d/mysqld [root@localhost ~]# chmod +x /etc/init.d/mysqld [root@localhost ~]# chkconfig --add mysqld [root@localhost ~]# chkconfig mysqld on [root@localhost ~]# service mysqld start 方法二 [root@localhost ~]# vim /usr/lib/systemd/system/mysqld.service [Unit] Description=Mysql After=syslog.target network.target remote-fs.target nss-lookup.target [Service] Type=forking PIDFile=/data/mysql/data/mysql.pid ExecStart=/usr/local/mysql/support-files/mysql.server start ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s QUIT $MAINPID PrivateTmp=false [Install] [root@localhost ~]# systemctl daemon-reload [root@localhost ~]# systemctl enable mysqld.service [root@localhost ~]# mysqladmin -u root -p password #重置密码 Enter password: New password: Confirm new password
环境变量
[root@localhost ~]# vim /etc/profile PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH export PATH [root@localhost ~]# source /etc/profile
msyql多实例(完全独立)
##准备数据目录 [root@localhost local]# mkdir /data/mysql/{3306,3307} [root@localhost local]# chown mysql:mysql /data/mysql/ [root@localhost local]# chown mysql:mysql /data/mysql/ [root@localhost local]# mv /usr/local/mysql /tmp [root@localhost local]# cd /usr/local/src/mysql-5.7.18/ [root@localhost mysql-5.7.18]# make install ##初始化数据库 [root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306 [root@localhost mysql-5.7.18]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307 ##配置数据库目录 [root@localhost mysql-5.7.18]# mv /usr/local/mysql /usr/local/mysql3306 [root@localhost mysql-5.7.18]# make install [root@localhost mysql-5.7.18]# mv /usr/local/mysql /usr/local/mysql3307 [root@localhost mysql-5.7.18]# cd /usr/local/ [root@localhost local]# cp /etc/my.cnf /usr/local/mysql3306 [root@localhost local]# cp /etc/my.cnf /usr/local/mysql3307 [root@localhost local]# mv /etc/my.cnf /etc/my.cnf.bak ##修改my.cnf文件中的路径 [root@localhost local]# vim mysql3306/my.cnf [client] port = 3306 socket = /data/mysql/3306/mysql.sock default-character-set = utf8 [mysqld] port = 3306 socket = /data/mysql/3306/mysql.sock basedir = /usr/local/mysql3306 datadir = /data/mysql/3306 pid-file = /data/mysql/3306/mysql.pid user = mysql bind-address = 0.0.0.0 server-id = 1 [root@localhost local]# vim mysql3307/my.cnf [client] port = 3307 socket = /data/mysql/3307/mysql.sock default-character-set = utf8 [mysqld] port = 3307 socket = /data/mysql/3307/mysql.sock basedir = /usr/local/mysql3307 datadir = /data/mysql/3307 pid-file = /data/mysql/3307/mysql.pid user = mysql bind-address = 0.0.0.0 server-id = 2 ##配置mysql 启动脚本 [root@localhost local]# vim mysql3306/support-files/mysql.server then basedir=/usr/local/mysql3306 bindir=/usr/local/mysql3306/bin if test -z "$datadir" then datadir=/data/mysql/3306 fi sbindir=/usr/local/mysql3306/bin libexecdir=/usr/local/mysql3306/bin [root@localhost local]# vim mysql3307/support-files/mysql.server then basedir=/usr/local/mysql3307 bindir=/usr/local/mysql3307/bin if test -z "$datadir" then datadir=/data/3307/data fi sbindir=/usr/local/mysql3307/bin libexecdir=/usr/local/mysql3307/bin ## 启动服务 [root@localhost local]# /usr/local/mysql3306/support-files/mysql.server start [root@localhost local]# /usr/local/mysql3307/support-files/mysql.server start [root@localhost local]# netstat -anpt Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 40724/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 47249/mysqld
mysqld_multi管理多实例
##环境准备 [root@localhost local]# mkdir /data/mysql/{3306,3307} [root@localhost local]# chown mysql:mysql /data/mysql/ [root@localhost local]# cd /usr/local/src/mysql-5.7.18/ [root@localhost mysql-5.7.18]# make install ## 修改my.cnf [root@localhost mysql-5.7.18]# vim /etc/my.cnf [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin log = /data/mysql/mysqld_multi.log [mysqld1] port = 3306 socket = /data/mysql/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql/3306 pid-file = /data/mysql/3306/mysql.pid user = mysql log_error = /data/mysql/3306/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/3306/mysql-slow.log general_log=ON general_log_file= /data/mysql/3306/mysql.log performance_schema = 0 explicit_defaults_for_timestamp [mysqld2] port = 3307 socket = /data/mysql/3307/mysql.sock basedir = /usr/local/mysql datadir = /data/mysql/3307 pid-file = /data/mysql/3307/mysql.pid user = mysql log_error = /data/mysql/3307/mysql-error.log slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/3307/mysql-slow.log general_log=ON general_log_file= /data/mysql/3307/mysql.log performance_schema = 0 explicit_defaults_for_timestamp ## 初始化数据库 # /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306 # /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307 ## 服务管理 # /usr/local/mysql/bin/mysqld_multi report #查看所有实例状态 # /usr/local/mysql/bin/mysqld_multi start #启动所有实例 # /usr/local/mysql/bin/mysqld_multi start 1 #启动实例1 # mysqladmin -uroot -prVrzzT9s -S /data/mysql/3306/mysql.sock shutdown #关闭某个实例 ## 设置密码 [root@localhost ~]# mysqladmin -u root -p password -P 3306 -S /data/mysql/3306/mysql.sock [root@localhost ~]# mysqladmin -u root -p password -P 3307 -S /data/mysql/3307/mysql.sock