mysql二进制安装,升级,多实例部署
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql二进制安装,升级,多实例部署,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含18869字,纯文字阅读大概需要27分钟。
内容图文
- 理解线上部署考虑的因素
- 学会编译安装以及二进制安装mysql
- 学会升级mysql
- 学会多实例部署mysql数据库
- 学会合理部署mysql线上库
- 版本选择,5.1,5.5还是5.6?
- 分支选择,官方社区版?percona server?mariadb?
-
- 推荐官方版,简单易上手
- 安装方式,包安装?二进制包安装?源码安装?
-
- 线上推荐二进制包安装
- 路径配置,参数配置(尽量模板化,标准化)
- 一个实例多个库,or多个实例单个库?
- 下载软件安装包
- 解压放到指定目录(比如/usr/local)并将mysql目录放到PATH中
- 初始化实例,编辑配置文件 并启动
- 账号安全设置
#tar -zxf mysql-5.5.50-linux2.6-i686.tar.gz #mv mysql-5.5.50-linux2.6-i686 /usr/local/ #cd /usr/local #mv mysql-5.5.50-linux2.6-i686 mysql55 #cd mysql55/bin #export PATH=/usr/local/mysql55/bin:$PATH
#mkdir -p /tmp/mysqldata/node1 #/usr/local/mysql55/script/mysql_install_db --user=mysql --basedir=/usr/local/mysql55 --datadir=/tmp/mysqldata/node1 #cd /tmp/mysqldata/node1
/tmp/mysql55/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared objectfile: No such file or directory
aiapple@aiapple:/tmp$ sudo apt-cache search libaio libaio-dev - Linux kernel AIO access library - development files libaio1 - Linux kernel AIO access library - shared library libaio1-dbg - Linux kernel AIO access library - debugging symbols
aiapple@aiapple:/tmp$ sudo apt-get install libaio1
aiapple@aiapple:/usr/local/mysql/data$ ls -l /tmp/mysqldata/node1/ total 12 drwx------ 2 aiapple aiapple 4096 7月 2721:27 mysql drwx------ 2 aiapple aiapple 4096 7月 2721:27 performance_schema drwx------ 2 aiapple aiapple 4096 7月 2721:25 test
cp /usr/local/mysql56/support-files/my-medium.cnf /tmp/mysqldata
/usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf &
aiapple@aiapple:/usr/local/mysql55/support-files$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf & [1] 3349 aiapple@aiapple:/usr/local/mysql55/support-files$ 16072721:33:26 mysqld_safe Logging to ‘/usr/local/mysql/data/aiapple.err‘. touch: cannot touch ‘/usr/local/mysql/data/aiapple.err’: No such file or directory chmod: cannot access ‘/usr/local/mysql/data/aiapple.err’: No such file or directory 16072721:33:26 mysqld_safe The file /usr/local/mysql/bin/mysqld does not exist or is not executable. Please cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe& See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information /usr/local/mysql55/bin/mysqld_safe: 128: /usr/local/mysql55/bin/mysqld_safe: cannot create /usr/local/mysql/data/aiapple.err: Directory nonexistent ^C [1]+ Exit 1 /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
aiapple@aiapple:/usr/local$ sudomkdir -p mysql/data aiapple@aiapple:/usr/local/mysql$ sudochown -R aiapple . aiapple@aiapple:/usr/local/mysql$ sudochgrp -R aiapple .
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf & [1] 3773 aiapple@aiapple:/usr/local/mysql$ 16072721:37:32 mysqld_safe Logging to ‘/usr/local/mysql/data/aiapple.err‘. 16072721:37:32 mysqld_safe The file /usr/local/mysql/bin/mysqld does not exist or is not executable. Please cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe& See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information ^C [1]+ Exit 1 /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf
aiapple@aiapple:/usr/local/mysql$ cp -ar ../mysql55/* .
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf & [1] 3983 aiapple@aiapple:/usr/local/mysql$ 16072721:39:05 mysqld_safe Logging to ‘/usr/local/mysql/data/aiapple.err‘. 16072721:39:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 16072721:39:07 mysqld_safe mysqld from pid file /usr/local/mysql/data/aiapple.pid ended [1]+ Done /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf 且mysqld没有启动 aiapple@aiapple:~$ ps -ef | grep mysqld aiapple 21292097019:35 pts/200:00:00grep --color=auto mysqld
160727 21:41:59 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 16072721:41:59 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.50-log) starting as process 4500 ... 16072721:41:59 [Note] Plugin ‘FEDERATED‘ is disabled. /usr/local/mysql/bin/mysqld: Table ‘mysql.plugin‘ doesn‘t exist16072721:41:59 [ERROR] Can‘t open the mysql.plugin table. Please run mysql_upgrade to create it.16072721:41:59 InnoDB: The InnoDB memory heap is disabled 16072721:41:59 InnoDB: Mutexes and rw_locks use GCC atomic builtins 16072721:41:59 InnoDB: Compressed tables use zlib 1.2.316072721:41:59 InnoDB: Using Linux native AIO 16072721:41:59 InnoDB: Initializing buffer pool, size = 128.0M16072721:41:59 InnoDB: Completed initialization of buffer pool 16072721:41:59 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 4894116072721:41:59 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 159567516072721:41:59 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899 InnoDB: Apply batch completed 16072721:41:59 InnoDB: Waiting for the background threads to start 16072721:42:00 InnoDB: 5.5.50 started; log sequence number 159567516072721:42:00 [Note] Recovering after a crash using mysql-bin 16072721:42:00 [Note] Starting crash recovery... 16072721:42:00 [Note] Crash recovery finished. 16072721:42:00 [Note] Server hostname (bind-address): ‘0.0.0.0‘; port: 330616072721:42:00 [Note] - ‘0.0.0.0‘ resolves to ‘0.0.0.0‘; 16072721:42:00 [Note] Server socket created on IP: ‘0.0.0.0‘. 16072721:42:00 [ERROR] Fatal error: Can‘t open and lock privilege tables: Table ‘mysql.host‘ doesn‘t exist 16072721:42:00 mysqld_safe mysqld from pid file /usr/local/mysql/data/aiapple.pid ended
aiapple@aiapple:/usr/local/mysql/data$ rm -rf test/ aiapple@aiapple:/usr/local/mysql/data$ cp -ar /tmp/mysqldata/node1/* .
aiapple@aiapple:/usr/local/mysql/data$ ps -ef | grep mysqld aiapple 45362146021:43 pts/000:00:00 /bin/sh /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf aiapple 47734536121:43 pts/000:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/aiapple.err --pid-file=/usr/local/mysql/data/aiapple.pid --socket=/tmp/mysql.sock --port=3306 aiapple 47922097021:44 pts/200:00:00grep --color=auto mysqld
[client] #password = your_password port = 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock 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
my.cnf port = 3306 socket = /tmp/mysqldata/node1/mysql.sock [mysqld_safe] user=aiapple # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /tmp/mysqldata/node1/mysql.sock pid-file = /tmp/mysqldata/node1/mysqld.pid basedir = /usr/local/mysql55 datadir = /tmp/mysqldata/node1 innodb_data_home_dir = /tmp/mysqldata/node1 innodb_log_group_home_dir =/tmp/mysqldata/node1 tmpdir = /tmp/mysqldata/node1 log-error = /tmp/mysqldata/node1/aiapple.log log-bin = /tmp/mysqldata/node1/mysql-bin.log
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf & Warning: World-writable config file‘/tmp/mysqldata/my.cnf‘ is ignored
aiapple@aiapple:sudochmod600 /tmp/mysqldata/my.cnf
aiapple@aiapple:/usr/local/mysql$ /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf & 16080616:07:29 mysqld_safe The file /usr/local/mysql/bin/mysqld does not exist or is not executable. Please cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe&
#my.cnf增加
ledir = /usr/local/mysql55
160806 16:48:46 [Note] Plugin ‘FEDERATED‘ is disabled. 16080616:48:46 InnoDB: The InnoDB memory heap is disabled 16080616:48:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins 16080616:48:46 InnoDB: Compressed tables use zlib 1.2.316080616:48:46 InnoDB: Using Linux native AIO 16080616:48:46 InnoDB: Initializing buffer pool, size = 128.0M
#my.cnf增加 nice = 0
mysql -uroot --socket=/tmp/mysql.sock select user,host,password from mysql.user; delete from mysql.user where user=‘‘; delete from mysql.user where host<>‘localhost‘; mysql> select user,host,password from mysql.user; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | root | localhost | | +------+-----------+----------+ 1 row in set (0.00 sec)
mysql> set password for root@‘localhost‘=password(‘000000‘); Query OK, 0 rows affected (0.00 sec)
flush privileges;
创建test用户 #mysql -uroot -p123456--socket=/tmp/mysql.sock grant select on *.* to test@‘localhost‘ identified by ‘123‘; 使用test用户,在test库中建表 #mysql -utest -p123 --socket=/tmp/mysql.sock use test create table t1(idint); mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.01 sec)
mysql> drop database test; Query OK, 1 row affected (0.01 sec)
- 下载mysql5.6安装包并配置mysql5.6安装包安装路径
- 关闭mysql5.5的实例,修改部分参数,使用mysql5.6软件启动
- 执行mysql5.6路径下mysql_upgrade脚本
- 验证是否成功升级
aiapple@aiapple:/tmp/mysqldata$ ps -ef | grep mysqld aiapple 49312146022:32 pts/000:00:00 /bin/sh /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf aiapple 51684931022:32 pts/000:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/usr/local/mysql/data/aiapple.err --pid-file=/usr/local/mysql/data/aiapple.pid --socket=/tmp/mysql.sock --port=3306 aiapple 51874889022:33 pts/1300:00:00grep --color=auto mysqld
aiapple@aiapple:/usr/local/mysql$ mysqladmin -uroot --socket=/tmp/mysql.sock shutdown -p Enter password: 16072722:28:46 mysqld_safe mysqld from pid file /usr/local/mysql/data/aiapple.pid ended [1]+ Done /usr/local/mysql55/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf (wd: /usr/local/mysql/data) (wd now: /usr/local/mysql)
aiapple@aiapple:/usr/local/mysql$ ps -ef | grep mysqld aiapple 49182146022:28 pts/000:00:00grep --color=auto mysqld
#vim my.cnf(把55目录都改成56的)
aiapple@aiapple:/tmp/mysqldata$ /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf & [1] 9121 aiapple@aiapple:/tmp/mysqldata$ 16080621:29:11 mysqld_safe Logging to ‘/tmp/mysqldata/node1/aiapple.log‘. 16080621:29:11 mysqld_safe Starting mysqld daemon with databases from /tmp/mysqldata/node1
aiapple@aiapple:/tmp/mysqldata$ ps -ef | grep mysql aiapple 91218547021:29 pts/200:00:00 /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf aiapple 94899121021:29 pts/200:00:00 /usr/local/mysql56/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node1 --plugin-dir=/usr/local/mysql56/lib/plugin --log-error=/tmp/mysqldata/node1/aiapple.log --pid-file=/tmp/mysqldata/node1/mysqld.pid --socket=/tmp/mysqldata/node1/mysql.sock --port=3306 aiapple 95648547021:35 pts/200:00:00grep --color=auto mysql
mysql> status -------------- mysql Ver 14.14 Distrib 5.6.32, for linux-glibc2.5 (x86_64) using EditLine wrapper Connection id: 3 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: ‘‘ Using delimiter: ; Server version: 5.6.32-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysqldata/node1/mysql.sock Uptime: 7 min 27 sec Threads: 1 Questions: 5 Slow queries: 0 Opens: 32 Flush tables: 1 Open tables: 25 Queries per second avg: 0.011 --------------
aiapple@aiapple:/tmp/mysqldata$ /usr/local/mysql56/bin/mysql_upgrade -uroot --socket=/tmp/mysqldata/node1/mysql.sock -p Enter password: Looking for‘mysql‘ as: /usr/local/mysql56/bin/mysql Looking for‘mysqlcheck‘ as: /usr/local/mysql56/bin/mysqlcheck Running ‘mysqlcheck‘ with connection arguments: ‘--socket=/tmp/mysqldata/node1/mysql.sock‘ Warning: Using a password on the command line interface can be insecure. Running ‘mysqlcheck‘ with connection arguments: ‘--socket=/tmp/mysqldata/node1/mysql.sock‘ Warning: Using a password on the command line interface can be insecure. mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running ‘mysql_fix_privilege_tables‘... Warning: Using a password on the command line interface can be insecure. Running ‘mysqlcheck‘ with connection arguments: ‘--socket=/tmp/mysqldata/node1/mysql.sock‘ Warning: Using a password on the command line interface can be insecure. Running ‘mysqlcheck‘ with connection arguments: ‘--socket=/tmp/mysqldata/node1/mysql.sock‘ Warning: Using a password on the command line interface can be insecure. OK
aiapple@aiapple:/tmp/mysqldata$ /usr/local/mysql56/bin/mysql_upgrade -uroot --socket=/tmp/mysqldata/node1/mysql.sock -p Enter password: Looking for‘mysql‘ as: /usr/local/mysql56/bin/mysql Looking for‘mysqlcheck‘ as: /usr/local/mysql56/bin/mysqlcheck This installation of MySQL is already upgraded to 5.6.32, use --force if you still need to run mysql_upgrade
- 部署好mysql软件
- 编辑多个配置文件,初始化多个实例
- 启动mysql实例
- 充分利用系统资源
- 资源隔离
- 业务,模块隔离
#mkdir -p /mysqldata/node3
aiapple@aiapple:/tmp/mysqldata/node1$ /usr/local/mysql56/scripts/mysql_install_db --user=aiapple --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node3 #查看 aiapple@aiapple:/tmp/mysqldata/node1$ ls ../node3 ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
cp my2.cnf my3.cnf vim my3.cnf(修改相关参数) [client] #password = your_password port = 3308 socket = /tmp/mysqldata/node3/mysql.sock [mysqld_safe] user=aiapple ledir = /usr/local/mysql56/bin #ledir = /usr/local/mysql55/bin nice = 0 # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3308 socket = /tmp/mysqldata/node3/mysql.sock pid-file = /tmp/mysqldata/node3/mysqld.pid basedir = /usr/local/mysql56 #basedir = /usr/local/mysql55 datadir = /tmp/mysqldata/node3 innodb_data_home_dir = /tmp/mysqldata/node3 innodb_log_group_home_dir = /tmp/mysqldata/node3 tmpdir = /tmp/mysqldata/node3 log-error = /tmp/mysqldata/node3/aiapple.log log-bin = /tmp/mysqldata/node3/mysql-bin.log
aiapple@aiapple:/tmp/mysqldata/node3$ /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/node3/my3.cnf & [2] 9861 aiapple@aiapple:/tmp/mysqldata/node3$ 16080622:03:17 mysqld_safe Logging to ‘/tmp/mysqldata/node3/aiapple.log‘. 16080622:03:17 mysqld_safe Starting mysqld daemon with databases from /tmp/mysqldata/node3
aiapple@aiapple:/tmp/mysqldata/node3$ ps -ef | grep mysqld aiapple 91218547021:29 pts/200:00:00 /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/my.cnf aiapple 94899121021:29 pts/200:00:02 /usr/local/mysql56/bin/mysqld --defaults-file=/tmp/mysqldata/my.cnf --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node1 --plugin-dir=/usr/local/mysql56/lib/plugin --log-error=/tmp/mysqldata/node1/aiapple.log --pid-file=/tmp/mysqldata/node1/mysqld.pid --socket=/tmp/mysqldata/node1/mysql.sock --port=3306 aiapple 98618547022:03 pts/200:00:00 /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/tmp/mysqldata/node3/my3.cnf aiapple 103279861022:03 pts/200:00:00 /usr/local/mysql56/bin/mysqld --defaults-file=/tmp/mysqldata/node3/my3.cnf --basedir=/usr/local/mysql56 --datadir=/tmp/mysqldata/node3 --plugin-dir=/usr/local/mysql56/lib/plugin --log-error=/tmp/mysqldata/node3/aiapple.log --pid-file=/tmp/mysqldata/node3/mysqld.pid --socket=/tmp/mysqldata/node3/mysql.sock --port=3308 aiapple 103508547022:04 pts/200:00:00grep --color=auto mysqld 可以看到有两个端口的mysqld,分别是3306,3308;
aiapple@aiapple:/tmp/mysqldata/node3$ mysql -uroot --socket=/tmp/mysqldata/node3/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.32-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘for help. Type ‘\c‘ to clear the current input statement. delete from msyql.user where user=‘‘; delete from mysql.user where host<>‘localhost‘; set password for root@‘localhost‘=password(‘000000‘); delete from mysql.db; drop database test; flush privileges;
- 根据需求选择合适的版本以及分支,建议使用或升级到较高版本5.5或5.6
- 如果需要定制mysql功能的话,可以考虑编译安装,否则的话建议使用二进制包安装,比较省事。
- 根据机器配置选择部署多个mysql实例还是单个实例,机器配置非常好的话,建议部署多实例
原文:http://www.cnblogs.com/Aiapple/p/5745045.html
内容总结
以上是互联网集市为您收集整理的mysql二进制安装,升级,多实例部署全部内容,希望文章能够帮你解决mysql二进制安装,升级,多实例部署所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。