1. 背景
同一台服务器，复制MySQL数据库。
2. 通过执行mysqldump命令完成复制
sudo mysqldump alidata_autonavi_1 -u root -ppassword –add-drop-table | mysql alidata_autonavi_2 -u root -ppassword;
其中alidata_autonavi_1表示原始数据库，alidata_autonavi_2表示复制后的数据库，password表示数据库密码，-ppassword中间不用空格。
如果MySQL配置完美，上述命令应该就直接执行成功，复制数据库完毕。
可惜，出现如下错误提示：
mysqldump:
Couldn’t execute ‘SET OPTION SQL_QUOTE_SHOW_CREATE=1’: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OPTION SQL_QUOTE_SHOW_CREATE=1’ at line 1 (1064)
原因：数据库版本问题，安装的是mysql5.6 、执行该命令是mysql5.5遗留下来的二进制文件。
解决：找到mysqldump的安装路径，上述命令带上路径重新执行。
sudo /opt/mysql/mysql-5.6.21/bin/mysqldump alidata_autonavi_1 -u root -ppassword –add-drop-table | mysql alidata_autonavi_2 -u root -ppassword;
虽然这次不报之前的异常，但是又出来另外一个异常：
mysqldump: Got error: 2002: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) when trying to connect
查了下，/tmp/mysql.sock根本不是我之前设置的路径，看看我之前设置的路径：
$cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It'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.
[mysqld]
# 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 = 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.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
basedir =/opt/mysql/mysql-5.6.21
datadir =/opt/mysql/data
user=mysql
character-set-server = utf8
symbolic-links=0
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
skip-name-resolve
innodb_buffer_pool_size=5G
innodb_log_file_size=500M
max_connections=1000
# 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 = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
从这里看得出，我的mysql.sock 路径为socket=/var/lib/mysql/mysql.sock，而不是其默认地址。因此执行命令时需要单独指定。
[dddd@e100069197189.zmf /opt/mysql/mysql-5.6.21/bin]
$sudo /opt/mysql/mysql-5.6.21/bin/mysqldump --socket=/var/lib/mysql/mysql.sock alidata_autonavi_1 -u root -ppassword --add-drop-table | mysql alidata_autonavi_2 -u root -ppassword;
Warning: Using a password on the command line interface can be insecure.
这一次，执行成功！查看了alidata_autonavi_2数据库，表和数据全部都复制好了。
3. 其他情况
复制数据库至远程服务器，在新数据库前加上远程服务器地址-h 192.168.1.168即可，如下：
[dddd@e100069197189.zmf /opt/mysql/mysql-5.6.21/bin]
$sudo /opt/mysql/mysql-5.6.21/bin/mysqldump --socket=/var/lib/mysql/mysql.sock alidata_autonavi_1 -u root -ppassword --add-drop-table | mysql -h 192.168.1.168 alidata_autonavi_2 -u root -ppassword;
4. MySQL不同数据库间同步数据表和数据
4.1 数据库间同步数据表
同步生产数据库的demand_management_large_version表到当前数据库，包含完整表结构和字断注释等。
CREATE TABLE demand_management_large_version LIKE production.demand_management_large_version;
4.2 数据库间同步数据
同步生产数据库的demand_score_item_score表中score_id >= 59的数据到当前数据库。
insert into demand_score_item_score
select * from production.demand_score_item_score t
where t.score_id >= 59;
4.3 备注说明
上述同步数据库处在同一台服务器上。
最后给你们推荐一个群，如果你还想提升自己，
欢迎加入Java技术交流群：659270626
本群提供免费的学习指导 提供Spring源码、MyBatis、Netty、Redis,Kafka、Mysql、Zookeeper、Tomcat、Docker、Dubbo、Nginx、分布式、高并发、性能调优、等架构技术架构资料 以及免费的解答
不懂的问题都可以在本群提出来 之后还会有职业生涯规划以及面试指导
网友评论