Mysql编译安装数据迁移升级部署手册
- 确定系统版本
Ubuntu 14.04.3 LTS (GNU/Linux 3.19.0-25-generic x86_64) - 确定mysql版本
Mysql-5.6.23 - ubuntu安装mysql目录
mysql安装目录 /usr/local/mysql
mysql数据目录 /usr/local/mysql/var/
mysql配置文件 /etc/my.cnf
mysql日志文件 /var/log/mysql/
mysql错误信息 /var/log/mysql/ - 原数据库备份（10G以内小数据量，大数据请按照后面主从故障恢复步骤的全备和增量备份模式）
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.38-0ubuntu0.12.04.1 (Ubuntu) Copyright (c) 2000, 2014, 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. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gearman | | gw_local | | mysql | | performance_schema | +--------------------+ 11 rows in set (0.00 sec) mysql> flush tables with read lock; //锁表，防止数据库更新写入 mysql> SET GLOBAL event_scheduler = off ; //关闭事件调度器 mysql> exit $ mkdir -p /opt/databak //创建备份目录 $ mysqldump -uyourusername -pyourpassword german > /opt/databak/gearman.sql $ mysqldump -uyourusername -pyourpassword gw_local > /opt/databak/gw_local.sql $ mysqldump -uyourusername -pyourpassword mysql > /opt/databak/mysql.sql $ cd /opt/databak $ ls gearman.sql gw_local.sql mysql.sql
- 删除旧版mysql5.5.43
$ service mysql stop $ dpkg -l | grep mysql //查看已经安装的mysql相关包 ii libmysqlclient-dev 5.5.43-0ubuntu0.12.04.1 MySQL database development files ii libmysqlclient18 5.5.43-0ubuntu0.12.04.1 MySQL database client library ii mysql-client 5.5.43-0ubuntu0.12.04.1 MySQL database client (metapackage depending on the latest version) ii mysql-client-5.5 5.5.38-0ubuntu0.12.04.1 MySQL database client binaries ii mysql-client-core-5.5 5.5.38-0ubuntu0.12.04.1 MySQL database core client binaries ii mysql-common 5.5.43-0ubuntu0.12.04.1 MySQL database common files, e.g. /etc/mysql/my.cnf ii mysql-server 5.5.44-0ubuntu0.12.04.1 MySQL database server (metapackage depending on the latest version) ii mysql-server-5.5 5.5.38-0ubuntu0.12.04.1 MySQL database server binaries and system database setup ii mysql-server-core-5.5 5.5.38-0ubuntu0.12.04.1 MySQL database server binaries $ apt-get purge -y mysql-client mysql-server mysql-common mysql-server-5.5 mysql-server-core-5.5 mysql-client-5.5 mysql-client-core-5.5 libmysqlclient-dev libmysqlclient18 $ rm -rf /etc/mysql/ //卸载删除mysql软件包和目录
- 安装新版mysql5.6.23
$ cd /usr/local/src //确定mysql5.6.23源码包已经上传到此路径 $ groupadd mysql //创建mysql用户组 $ useradd -s /sbin/nologin -M -g mysql mysql //创建mysql用户 $ apt-get install libncurses5-dev cmake //获取mysql编译安装依赖 $ tar zxvf mysql-5.6.32.tar.gz $ cd mysql-5.6.32 $ patch -p1 < ../mysql-openssl.patch //给安装源码打支持openssl的补丁，确定补丁文件已经上传到此处 $ cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=1 -DWITH_SSL=bundled -DWITH_ZLIB=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 ${MySQL55MAOpt} $ make && make install# vi /etc/my.cnf //创建编辑一个mysq配置文件的通用模板文件，内容如下 # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # MySQL programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, see: # http://dev.mysql.com/doc/mysql/en/option-files.html # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [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 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # binary logging format - mixed recommended binlog_format=mixed # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 #loose-innodb-trx=0 #loose-innodb-locks=0 #loose-innodb-lock-waits=0 #loose-innodb-cmp=0 #loose-innodb-cmp-per-index=0 #loose-innodb-cmp-per-index-reset=0 #loose-innodb-cmp-reset=0 #loose-innodb-cmpmem=0 #loose-innodb-cmpmem-reset=0 #loose-innodb-buffer-page=0 #loose-innodb-buffer-page-lru=0 #loose-innodb-buffer-pool-stats=0 #loose-innodb-metrics=0 #loose-innodb-ft-default-stopword=0 #loose-innodb-ft-inserted=0 #loose-innodb-ft-deleted=0 #loose-innodb-ft-being-deleted=0 #loose-innodb-ft-config=0 #loose-innodb-ft-index-cache=0 #loose-innodb-ft-index-table=0 #loose-innodb-sys-tables=0 #loose-innodb-sys-tablestats=0 #loose-innodb-sys-indexes=0 #loose-innodb-sys-columns=0 #loose-innodb-sys-fields=0 #loose-innodb-sys-foreign=0 #loose-innodb-sys-foreign-cols=0 # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /usr/local/mysql/data #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #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 # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
//添加一些扩展参数，并将默认存储引擎设备为支持事务的innodb $ sed '/skip-external-locking/i\datadir = /usr/local/mysql/var' -i /etc/my.cnf $ sed -i 's:#innodb:innodb:g' /etc/my.cnf $ sed -i 's:/usr/local/mysql/data:/usr/local/mysql/var:g' /etc/my.cnf $ /usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --user=mysql $ chown -R mysql /usr/local/mysql/var $ chgrp -R mysql /usr/local/mysql/. $ cp support-files/mysql.server /etc/init.d/mysql //复制创建启动文件 $ chmod 755 /etc/init.d/mysql $ vi /etc/ld.so.conf.d/mysql.conf /usr/local/mysql/lib /usr/local/lib :wq $ 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 $ /etc/init.d/mysql start $ /usr/local/mysql/bin/mysqladmin -u root password 123456 $ vi /tmp/mysql_sec_script //创建一个临时脚本，将mysql的正式用户和密码更新并保存（此处root用户为例） use mysql; update user set password=password('yourpassword') where user='root'; delete from user where not (user='root') ; delete from user where user='root' and password=''; drop database test; DROP USER ''@'%'; flush privileges; :wq $ /usr/local/mysql/bin/mysql -u root -p123456 -h localhost < /tmp/mysql_sec_script $ rm -f /tmp/mysql_sec_script $ echo -e "\nexpire_logs_days = 10" >> /etc/my.cnf $ sed -i '/skip-external-locking/a\max_connections = 1000' /etc/my.cnf $ service mysql restart至此，新的mysql5.6.23安装完毕，利用
ps -ef | grep mysql检查相应进程id - 创建数据库还原数据（注意非常重要：如果数据库需要做主从同步，请将此过程放在下面的主从同步实现以后！）
$ cd /opt/databak $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 207584 Server version: 5.6.23-log Source distribution Copyright (c) 2000, 2015, 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. mysql> create database gearman; //创建需要还原的数据库，此处以gearman为例 mysql> source gearman.sql //导入还原数据，其它库类似
0 条评论