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
mysql 5.1.71升级到5.6.30
生活不会突变,你要做的只是耐心和积累。人这一辈子没法做太多的事情,所以每一件都要做得精彩绝伦。你的时间有限,做喜欢的事情会令人愉悦,所以跟随自己的本心。