因使用源码安装的MySQL5.7.28多实例,在导入数据库时会出现问题,所以重新研究使用mysql_multi的方法来管理多实例,经过测试环境验证之后,在各方面使用上特别在备份还原上,没有报MySQL5.7.28多实例的问题,踩了不少坑,这里我将我的部署过程分享下,如果在哪里出问题的,还请多多指正与指导,谢谢!!
参考文章:Centos7.5安装mysql5.7.24二进制包方式部署
https://www.jb51.net/article/151867.htm
本从就直接从2.7章节开始安装mysql多实例,具体部署过程如下:
2.7 安装mysql多实例
2.7.1. 创建软件安装目录(部署路径请根据实际修改)
[root@~]# mkdir -pv /data/mysql/{3306,3307} [root@~]# mkdir -v /data/mysql/3306/{logs,data,binlog} [root@~]# mkdir -v /data/mysql/3307/{logs,data,binlog}
2.7.2. MySQL安装包下载
[root@~]# cd /opt [root@~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz [root@~]# tar zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz [root@~]# mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql [root@~]# chown -R mysql:mysql /usr/local/mysql [root@~]# chown -R mysql:mysql /data
2.7.3. MySQL参数配置
"htmlcode">
[root@~]# vim /etc/my.cnf [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld mysqladmin = /usr/local/mysql/bin/mysqladmin log = /data/mysql/mysqld_multi.log #user=root #pass= [mysql] prompt="\u@jsshapp \R:\m:\s [\d]> " no-auto-rehash [mysqld3306] user = mysql port = 3306 symbolic-links = 0 #basedir = /usr/ datadir = /data/mysql/3306/data socket = /data/mysql/3306/mysql3306.sock pid-file = /data/mysql/3306/mysqld3306.pid server_id = 3306 character_set_server = utf8 max_connections = 1000 skip_name_resolve = 1 open_files_limit = 65536 thread_cache_size = 64 table_open_cache = 4096 table_definition_cache = 1024 table_open_cache_instances = 64 max_prepared_stmt_count = 1048576 explicit_defaults_for_timestamp = true log_timestamps = system binlog_format = row log_bin = /data/mysql/3306/binlog/mysql-bin binlog_rows_query_log_events = on expire_logs_days = 7 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G sync_binlog = 1 log_bin_trust_function_creators = 1 slow_query_log = on slow_query_log_file = /data/mysql/3306/data/slow.log log-error = /data/mysql/3306/logs/error.log log_queries_not_using_indexes = on long_query_time = 1.000000 gtid_mode = on enforce_gtid_consistency = on default_storage_engine = innodb default_tmp_storage_engine = innodb innodb_data_file_path = ibdata1:12M:autoextend:max:2000M innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M innodb_buffer_pool_filename = ib_buffer_pool innodb_log_files_in_group = 3 innodb_log_file_size = 512M innodb_online_alter_log_max_size = 1024M innodb_open_files = 4096 innodb_page_size = 16k innodb_thread_concurrency = 0 innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_print_all_deadlocks = on innodb_lock_wait_timeout = 20 innodb_spin_wait_delay = 128 innodb_autoinc_lock_mode = 2 innodb_io_capacity = 200 innodb_io_capacity_max = 2000 #innodb_flush_neighbors = innodb_log_buffer_size = 8M innodb_flush_log_at_timeout = 1 innodb_flush_log_at_trx_commit = 2 innodb_buffer_pool_size = 1024M innodb_buffer_pool_instances = 4 autocommit = 1 innodb_buffer_pool_dump_pct = 25 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON [mysqld3307] user = mysql port = 3307 symbolic-links = 0 lower_case_table_names = 1 #basedir = /usr/ datadir = /data/mysql/3307/data socket = /data/mysql/3307/mysql3307.sock pid-file = /data/mysql/3307/mysqld3307.pid server_id = 3307 character_set_server = utf8 max_connections = 1000 skip_name_resolve = 1 open_files_limit = 65536 thread_cache_size = 64 table_open_cache = 4096 table_definition_cache = 1024 table_open_cache_instances = 64 max_prepared_stmt_count = 1048576 explicit_defaults_for_timestamp = true log_timestamps = system binlog_format = row log_bin = /data/mysql/3307/binlog/mysql-bin binlog_rows_query_log_events = on expire_logs_days = 7 binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G sync_binlog = 1 slow_query_log = on slow_query_log_file = /data/mysql/3307/data/slow.log log-error = /data/mysql/3307/logs/error.log log_queries_not_using_indexes = on long_query_time = 1.000000 gtid_mode = on enforce_gtid_consistency = on default_storage_engine = innodb default_tmp_storage_engine = innodb innodb_data_file_path = ibdata1:12M:autoextend:max:2000M innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:2000M innodb_buffer_pool_filename = ib_buffer_pool innodb_log_files_in_group = 3 innodb_log_file_size = 512M innodb_online_alter_log_max_size = 1024M innodb_open_files = 4096 innodb_page_size = 16k innodb_thread_concurrency = 0 innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_print_all_deadlocks = on innodb_lock_wait_timeout = 20 innodb_spin_wait_delay = 128 innodb_autoinc_lock_mode = 2 innodb_io_capacity = 200 innodb_io_capacity_max = 2000 #innodb_flush_neighbors = innodb_log_buffer_size = 8M innodb_flush_log_at_timeout = 1 innodb_flush_log_at_trx_commit = 2 innodb_buffer_pool_size = 1024M innodb_buffer_pool_instances = 4 autocommit = 1 innodb_buffer_pool_dump_pct = 25 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON [mysqldump] quick max_allowed_packet = 32M
2.7.4. 配置MySQL环境变量
[root@~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' /etc/profile [root@~]# tail -1 /etc/profile [root@~]# source /etc/profile
2.7.5. testone实例初始化
"htmlcode">
[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3306/data/ > /tmp/3306.log 2>&1 [root@~]# tail -100f /tmp/3306.log ---使用tail命令查看初始化日志,有出现如下内容,即表示初始化完成(其中#5+t+xYW+<t"htmlcode">[root@~]# mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data"htmlcode">
[root@~]# mysqld_multi start 3306 [root@~]# tail -100f /data/mysql/3306/logs/error.log ----使用tail命令查看启动日志,有出现即表示启动成功 Version: '5.7.28-log' socket: '/data/mysql/3306/mysql3306.sock' port: 3306 MySQL Community Server (GPL)"htmlcode">
[root@~]# less /tmp/3306.log | grep 'A temporary password' [root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock Enter password: mysql> alter user 'root'@'localhost' identified by '统一密码'; mysql> flush privileges; mysql> exit;"htmlcode">
[root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema Enter password:"htmlcode">
[root@~]# sed -i "s@^#user=root@user=root@g" /etc/my.cnf [root@~]# sed -i "s@^#pass=@pass=统一密码@g" /etc/my.cnf [root@~]# cat /etc/my.cnf | grep pass= ---使用cat命令查看配置文件pass字段输出的结果是否一致"htmlcode">
[root@~]# mysqld_multi stop 3306 [root@~]# netstat -tnlp|grep 3306 ---输入结果为空表示服务停止正常2.7.6. testtwo实例初始化
"htmlcode">
[root@~]# mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql/ --user=mysql --datadir=/data/mysql/3307/data/ > /tmp/3307.log 2>&1 [root@~]# tail -100f /tmp/3307.log ---使用tail命令查看初始化日志,有出现如下内容,即表示初始化完成(其中-pn>t;Ye)Ay6=I即为root用户的临时密码) A temporary password is generated for root@localhost: -pn>t;Ye)Ay6=I "htmlcode">[root@~]# mysqld_multi start 3307----使用tail命令查看启动日志,有出现即表示启动成功
[root@~]# tail -100f /data/mysql/3307/logs/error.log Version: '5.7.28-log' socket: '/data/mysql/3307/mysql3307.sock' port: 3307 MySQL Community Server (GPL)"htmlcode">
[root@~]# less /tmp/3307.log|grep 'A temporary password' [root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock Enter password: mysql> alter user 'root'@'localhost' identified by '统一密码'; mysql> flush privileges; mysql> exit;"htmlcode">
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schemaEnter password:
"htmlcode">
[root@~]# mysqld_multi stop 3307 [root@~]# netstat -tnlp|grep 3307 ---输入结果为空表示服务停止正常2.7.7. mysqld_multi多实例管理命令
"htmlcode">
[root@~]# mysqld_multi stop 3306 [root@~]# mysqld_multi stop 3307"htmlcode">
[root@~]# mysqld_multi start 3306 [root@~]# mysqld_multi start 3307"htmlcode">
[root@~]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running"htmlcode">
[root@~]# mysqld_multi report 3306 Reporting MySQL servers MySQL server from group: mysqld3306 is running [root@~]# mysqld_multi report 3307 Reporting MySQL servers MySQL server from group: mysqld3307 is running"htmlcode">
[root@~]# mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running(3)进入testone的mysql数据库创建testone数据库实例、用户名及密码并导入数据
[root@~]# cd /data/ [root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock Enter password: mysql>create database testone default character set utf8 collate utf8_bin; mysql> grant select,insert,update,delete,create,alter,execute on testone.* to 'testone'@'%' identified by '密码'; mysql> flush privileges; mysql> exit [root@~]# mysql -uroot -p -S /data/mysql/3306/mysql3306.sock testone < /data/test.sql(3)进入testtwo的mysql数据库,创建testtwo服务数据库实例、用户名与密码并导入数据
[root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock Enter password: mysql> create database testtwo default character set utf8 collate utf8_bin; mysql> grant select,insert,update,delete,create,alter,execute on testtwo.* to 'testtwo'@'%' identified by '密码'; mysql> flush privileges; mysql> exit [root@~]# mysql -uroot -p -S /data/mysql/3307/mysql3307.sock testtwo < /data/testtwo.sql2.7.9. 防火墙配置
"htmlcode">
[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent [root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent"color: #ff0000">总结
以上所述是小编给大家介绍的CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!
更新动态
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]