>>与软件开发有关的知识:操作系统,数据库,网络通信等 书籍支持  卫琴直播  品书摘要  在线测试  资源下载  联系我们
发表一个新主题 开启一个新投票 回复文章 您是本文章第 20778 个阅读者 刷新本主题
 * 贴子主题:  CentOS7.5使用mysql_multi方式安装MySQL5.7.28多实例 回复文章 点赞(0)  收藏  
作者:flybird    发表时间:2020-01-27 22:34:23     消息  查看  搜索  好友  邮件  复制  引用

  
因使用源码安装的MySQL5.7.28多实例,在导入数据库时会出现问题,所以重新研究使用mysql_multi的方法来管理多实例,经过测试环境验证之后,在各方面使用上特别在备份还原上,没有报MySQL5.7.28多实例的问题,踩了不少坑,这里我将我的部署过程分享下,如果在哪里出问题的,还请多多指正与指导,谢谢!!

关于mysql安装部署基础环境与配置可以参考文章:CentOS7.5源码安装MySQL5.7.28多实例
https://blog.51cto.com/8355320/2463218

本文先介绍安装mysql多实例,具体部署过程如下:

安装mysql多实例

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.  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

   3.  MySQL参数配置
    配置my.cnf参数文件
(1)server_id=3306与server_id=3307数值请根据实际配置,注意配置的id值与局域网内其他各实例所配置的数值不可以冲突;
(2)max_connections=1000配置MySQL数据库的最大连接数,根据实际需要配置,其他参数的优化根据实际需要修改或添加;
(3)配置文件全部内容如下    

[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

   4.  配置MySQL环境变量    

[root@~]#  echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
[root@~]#  tail -1 /etc/profile
[root@~]#  source /etc/profile

   5.  testone实例初始化
    操作实例初始化    

[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 即为root用户的临时密码)
A temporary password is generated for root@localhost: #5+t+xYW+<t

         生成ssl文件    

[root@~]#  mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data

         启动testone实例    

[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)

         修改root密码    

[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;

      验证 testone root用户统一密码(界面正常输出information_schema内容表示正常)    

[root@~]#  mysql -uroot -p -S /data/mysql/3306/mysql3306.sock -e "show databases;" | grep information_schema
Enter password:

      修改my.cnf配置文件,将修改的统一密码,添加到配置文件中    

[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字段输出的结果是否一致

      停止testone实例    

[root@~]#  mysqld_multi stop 3306
[root@~]#  netstat -tnlp|grep 3306         ---输入结果为空表示服务停止正常

   6.  testtwo实例初始化
    操作实例初始化    

[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
    生成ssl文件    

[root@~]#  mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data

         启动testwo实例    

[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)

      修改root密码    

[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;

      验证 testtwo root用户统一密码(界面正常输出information_schema内容表示正常)    

[root@~]#  mysql -uroot -p -S /data/mysql/3307/mysql3307.sock -e "show databases;" | grep information_schema
Enter password:

      停止testtwo实例    

[root@~]#  mysqld_multi stop 3307
[root@~]#  netstat -tnlp|grep 3307         ---输入结果为空表示服务停止正常

   7.  mysqld_multi多实例管理命令
    启动全部实例    

[root@~]#  mysqld_multi start

         停止单个实例    

[root@~]#  mysqld_multi stop 3306
[root@~]#  mysqld_multi stop 3307

      启动单个实例    

[root@~]#  mysqld_multi start 3306
[root@~]#  mysqld_multi start 3307

      查看全部实例的状态(is running)    

[root@~]#  mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

      查看单个实例状态    

[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

      停止全部实例    

[root@~]#  mysqld_multi stop

      8.  数据导入
(1)将待导入的数据库脚本(test.sql、testtwo.sql)文件(名称根据实际情况操作)上传至/data/路径下
(2)使用命令确认两实例已经处于运行状态    

[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.sql

   9.  防火墙配置
    根据实际要求,添加开放端口    

[root@~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent

      重新载入    

[root@~]# firewall-cmd --reload

----------------------------
原文链接:https://blog.51cto.com/8355320/2466817
原作者:平凡的平夏

程序猿的技术大观园:www.javathinker.net



[这个贴子最后由 flybird 在 2020-01-29 12:09:50 重新编辑]
网站系统异常


系统异常信息
Request URL: http://www.javathinker.net/WEB-INF/lybbs/jsp/topic.jsp?postID=1625

java.lang.NullPointerException

如果你不知道错误发生的原因,请把上面完整的信息提交给本站管理人员