mysql 5.1.71升级到5.6.30

mysql 5.1.71升级到5.6.30

mysqldump -h主机名  -P端口 -u用户名 -p密码 (–database) 数据库名 > 文件名.sql

备份MySQL数据库的命令
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库
mysqldump -hhostname -uusername -ppassword --databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构
mysqldump –-no-data --databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库
mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令
mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器
mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

1、备份配置文件
[root@webmaildb01 ~]# cat /etc/my.cnf | egrep -v "^$|^#"
[mysqld]
skip-name-resolve
port                    = 3306
skip-external-locking
query-cache-type    = 1
query-cache-size    = 64M
query_cache_limit   = 10M
thread_cache_size   = 8
innodb_file_per_table
ssl-cert = /etc/pki/tls/certs/iRedMail.crt
ssl-key = /etc/pki/tls/private/iRedMail.key
ssl-cipher = ALL
server-id                   = 1
log_bin                 = /var/log/mysqld/mysql-bin.log
log-slave-updates
log-bin-index           = /var/log/mysqld/mysqld-log-bin.index
log-error               = /var/log/mysqld/error.log
relay-log               = /var/log/mysqld/relay.log
relay-log-info-file     = /var/log/mysqld/relay-log.info
relay-log-index         = /var/log/mysqld/relay-log.index
auto_increment_increment = 10
auto_increment_offset   = 1
binlog_do_db            = amavisd
binlog_do_db            = iredadmin
binlog_do_db            = roundcubemail
binlog_do_db            = vmail
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=iredapd
binlog-ignore-db=sogo
binlog-ignore-db=test
log-slave-updates
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=iredapd
[client]
default-character-set=utf8
[mysqld_safe]
log-error   = /var/log/mysqld/mysqld.log

##############################################################

[root@webmaildb02 ~]# cat /etc/my.cnf | egrep -v "^$|^#"
[mysqld]
port                    = 3306
skip-external-locking
query-cache-type    = 1
query-cache-size    = 64M
query_cache_limit   = 10M
thread_cache_size   = 8
innodb_file_per_table
ssl-cert = /etc/pki/tls/certs/iRedMail.crt
ssl-key = /etc/pki/tls/private/iRedMail.key
ssl-cipher = ALL
skip-name-resolve
server-id                   = 2
log_bin                 = /var/log/mysqld/mysqld-bin.log
log-slave-updates
log-bin-index           = /var/log/mysqld/log-bin.index
log-error               = /var/log/mysqld/error.log
relay-log               = /var/log/mysqld/relay.log
relay-log-info-file     = /var/log/mysqld/relay-log.info
relay-log-index         = /var/log/mysqld/relay-log.index
auto_increment_increment = 10
auto_increment_offset = 1
binlog_do_db            = amavisd
binlog_do_db            = iredadmin
binlog_do_db            = roundcubemail
binlog_do_db            = vmail
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=iredapd
binlog-ignore-db= sogo
log-slave-updates
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=iredapd
[client]
default-character-set=utf8
[mysqld_safe]
log-error   = /var/log/mysqld/mysqld.log


2、关闭数据库，备份全部数据库,和部分数据库，表结构
######################################################################
同时备份多个MySQL数据库
mysqldump -hhostname -uusername -ppassword –-databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构
mysqldump –-no-data –-databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库
mysqldump -uroot -p密码 --all-databases > allbackupfile.sql

还原MySQL数据库的命令
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
############################################################################
先备份全库
mysqldump -uroot -p密码 --all-databases > allbackupfile.sql

备份mysql,amavisd,iredadmin,iredapd,roundcubemail,vmail
mysqldump -uroot -p密码 --databases amavisd iredadmin iredapd roundcubemail vmail mysql > multibackupfile.sql

备份表结构
mysqldump -uroot -p密码 --no-data --databases amavisd iredadmin iredapd roundcubemail vmail mysql > structurebackupfile.sql

2.1、删除/var/lib/mysql所有文件
     删除/var/log/mysqld/下的所有的文件

3、上传数据包
安装升级包，如果遇到冲突的包就卸载他,本次遇到的冲突包为mysql-libs


4、还原数据库
SET PASSWORD = PASSWORD('密码');
还原MySQL数据库的命令
mysql -uroot -p密码
mysql> source /root/allbackupfile.sql