How can I export the privileges from MySQL and then import to a new server?

 Linux, MySQL, 資料庫  No Responses »
12 月 212015
 

Source: How can I export the privileges from MySQL and then import to a new server?

I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server.

For extra points, there are a couple of existing databases on the new one already, how do I import the old servers privileges without nuking the couple existing of ones.

Old server: 5.0.67-community

New server: 5.0.51a-24+lenny1

EDIT: I've got a dump of the db 'mysql' from the Old Server & now want to know the proper way to merge with the 'mysql' db on the New Server.

I tried a straight 'Import' using phpMyAdmin and ended up with an error regarding a duplicate (one that I've already migrated manually).

Anyone got an elegant way of merging the two 'mysql' databases?

Continue reading »

MySql export schema without data

 Linux, MySQL  No Responses »
11 月 142014
 

原文(source): sql - MySql export schema without data - Stack Overflow

You can do with the --no-data option with mysqldump command

mysqldump -u root -p --no-data dbname > schema.sql

 

3
IMHO, mysqldump is the best answer. MySQL Administrator is abandoned and MySQL Workbench is still quite buggy. –  Álvaro G. Vicario May 30 '11 at 11:35
5
Also consider using the --single-transaction option if you don't want or can't do table locks. –  Jim Jan 22 '13 at 21:55
-d is --no-data for short. –  marstone Oct 8 at 17:15

MySQL 用 MySQLDump 備份 InnoDB 注意事項

 MySQL, 資料庫  No Responses »
8 月 142014
 

原文(source): MySQL 用 MySQLDump 備份 InnoDB 注意事項 | 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY

大家在備份 MySQL 資料庫時一定是使 mysqldump 指令，不管是 MyISAM 或 InnoDB 都一樣， 在處理 InnoDB 格式備份時使用 mysqldump -single-transaction，但是你會發現在大多的備份狀況都是 OK 的，只是有時候會發現有的資料表只有備份到 structure 而無備份到 Data？

Continue reading »

MySQL Import / 資料還原 忽略錯誤訊息

 Linux, MySQL, 程式設計  No Responses »
1 月 212009
 

MySQL Import / 資料還原 忽略錯誤訊息 | Tsung's Blog

要將 MySQL 的資料備份還原(mysqldump), 但是出現下面這些錯誤訊息: (mysql -u root DBNAME < DB.sql)

ERROR 1062 (23000) at line 43238: Duplicate entry '?' for key 2
ERROR 1062 (23000) at line 50788: Duplicate entry '?' for key 2
ERROR 1062 (23000) at line 50860: Duplicate entry '?' for key 2
ERROR 1062 (23000) at line 50923: Duplicate entry '?' for key 2

平常狀況這個應該是不存在的(或許吧? 業主的 DB 不知道為何會匯出這樣子的資料), 總之要先 Import 進去, 才能開始進行開發的工作.

Continue reading »