之前一直使用的navicat的自动的备份功能。但是在使用navicat的还原功能时&＃xff0c;贼慢。
今天要做的是window服务器上将mysql单个数据库备份。然后还原到我的mac笔记本上。
本来以为很快的&＃xff0c;中间遇到来一些小坑&＃xff0c;搞了很久。
1. 备份&＃xff0c;从window服务器使用mysqldump命令来备份&＃xff0c;由于后面还要下载到本地&＃xff0c;所以备份同时要进行压缩&＃xff0c;不然数据量很大&＃xff0c;呆会下载要很久。
D:\tool\mysql\bin\mysqldump.exe -uusername -ppassword dbname --default-character-set&＃61;UTF8| gzip >d:\backup\dbname_%date:~0,4%%date:~5,2%%date:~8,2%.gz
上面的gzip命令&＃xff0c;需要自己先在服务器安装上gzip的window版本。然后在环境变量中添加bin目录到path路径下。
最后 > 后面是备份到的目录和文件名&＃xff0c;文件名根据日期自动命名。这里最好指定导出时的字符集&＃xff0c;不然中文会出现乱码情况。
2. 还原 下载到本地&＃xff0c;解压缩还原。
mysql -h192.168.0.101 -uusername -p"password" --default-character-set&＃61;utf8 -f dbname
这里我在mac上开来一个虚机window&＃xff0c;然后链接到mac上的mysql&＃xff0c;所以192.168.0.101是我mac的ip地址&＃xff0c;同样指定字符集utf8.
3. 安装 mysql安装教程很多&＃xff0c;这里说下window下一个最便捷的安装方式。
首先下载那种直接解压缩版本的&＃xff0c;下载地址如下&＃xff0c;之前我用的大部分是5.5或者5.6版本&＃xff1a;
https://dev.mysql.com/downloads/mysql/5.5.html#downloads
解压缩到d:\tool\目录下&＃xff0c;文件夹命名为mysql。
然后将以下三个脚本拷贝到目录&＃xff0c;即可完成一键安装(双击setup&start.bat)&＃xff0c;卸载(双击stop&remove.bat)。在其他电脑上安装时&＃xff0c;下好包直接拷贝脚本过去即可。
1&＃xff09; 配置文件&＃xff1a;my.ini
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It&＃39;s a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.[client][mysql]
default-character-set&＃61;"utf8"
[mysqld]
character_set_server&＃61;"utf8" # Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size &＃61; 128M# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin# These are commonly set, remove the # and set as required.#设置mysql的安装目录
basedir&＃61;d:/tool/mysql#设置mysql数据库的存放目录
datadir&＃61;d:/tool/mysql/data#绑定IPV4和3306端口
port&＃61;3306# server_id &＃61; .....#允许最大连接数
max_connections&＃61;200# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size &＃61; 128M
# sort_buffer_size &＃61; 2M
# read_rnd_buffer_size &＃61; 2M #sql_mode&＃61;NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#default-storage-engine&＃61;MYISAM
default-storage-engine&＃61;INNODB#定义事务隔离级别
#可选参数有&＃xff1a;READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
transaction-isolation &＃61; REPEATABLE-READ#临时增加&＃xff0c;测试早上是否还存在断开的情况&＃xff0c;解决mysql8小时无连接自动关闭的情况
interactive_timeout&＃61;28800000
wait_timeout&＃61;28800000
2&＃xff09;安装和启动脚本 setup&start.bat
cd d:
cd d:\tool\mysql\bin\
mysqld --install mysql --defaults-file&＃61;"D:\tool\mysql\my.ini"
net start mysql
pause
3&＃xff09;停止服务和卸载脚本 stop&remove.bat
cd d:
cd d:\tool\mysql\bin\
net stop mysql
mysqld remove
pause
京公网安备 11010802041100号