CODEWITHSUNDEEP
mysqlphpmyadminmysql-8.0
Moinnou
Moinnou

Reputation: 11

Mysqldump/Mysqlpump - Addind database exception - MYSQL 8.0 PHPMYADMIN

I'm currently trying create a script to dump MYSQL database to add D-7 or D-1 server (Backup server)

I'm using MYSQL 8.0 - mysqlPUMP (and not dump) because it has a --exclude-databases variables. But, there is a little issue when i try to reimport the dump because all the request (Create, Atler, Add column, drop...) are increment randomly and i recieve oftenly error because of that like :

ERROR 1075 (42000) at line 1140: Incorrect table definition; there can be only one auto column and it must be defined as a key

I check in the dump file and the column AUTO-INCREMENT is already on PRIMARY KEY.

Before 8.0, i used mysqldump but the way to exclude databases is really hard.

Here is my script to Export/Import the Databases From 'A' (Production Server) to 'B' (Backup D-1 Server) :

REM Today date in variables
For /f "tokens=1-3 delims=/ " %%a in ('date /t') do (set mydate=%%a-%%b-%%c)

REM Export rights and users from Server A
mysqldump -h ServerA_IP  --port=3306 --user=importuser --password=xxxxxxxxx --opt mysql user --result-file "D:\DumpsToImport\dump mysql users.sql" 
mysqldump -h ServerA_IP  --port=3306 --user=importuser --password=xxxxxxxxx --opt mysql db --result-file "D:\DumpsToImport\dump mysql db.sql" 

REM Export Database except 5 from Server A
mysqlpump -h ServerA_IP  --port=3306 --exclude-databases=mysql,performance_schema,information_schema,sys,phpmyadmin --add-drop-table --user=importuser --password=XitbLxxxxxxxxx L5r8 > D:\DumpsToImport\dump-MySQL03-%mydate%.sql

REM Import rights and users to Server B
mysql -h ServerB_IP  --port=3306 --user=importuser --password=xxxxxxxxx mysql < "D:\DumpsToImport\dump mysql users.sql"
mysql -h ServerB_IP --port=3306 --user=importuser --password=xxxxxxxxx mysql < "D:\DumpsToImport\dump mysql db.sql" 

REM Reset rights
mysql -h ServerB_IP --port=3306 --user=importuser --password=xxxxxxxxx -e "FLUSH PRIVILEGES" 

REM Import DUMP .sql to Server B
mysql -h ServerB_IP --port=3306 --user=importuser --password=xxxxxxxxx < "D:\DumpsToImport\dump-MySQL03-%mydate%.sql"

This script work perfectly to create the DUMP.sql.

Here is the script I used before but didn't work anymore on MYSQL 8.0 REM Export Database except 5 from Server A is the part i would like to use :

REM Export Database except 5 from Server A

for /f "delims=" %%A in ('mysql -h mysql02.francelink.net -u dumpuser -pxxxxxxxxx -e "SELECT GROUP_CONCAT(schema_name SEPARATOR '\" \"') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema')"') do @set tablestodump=%%A

mysqldump -h ServerA_IP -udumpuser -pxxxxxxxxxxx --opt --databases "%tablestodump%" --result-file "D:\DumpsToImport\dump-MySQL02-%mydate%.sql"

This part didn't work correctly maybe because i'm on Mysql Server 8.0

Did you know something i could change ?

Upvotes: 1

Views: 83

Answers (1)

Moinnou
Moinnou

Reputation: 11

I use the old script but i change all the rights of my user importuser and it works

Mysqlpump is usefull to DUMP a database but not to re-import it after

Upvotes: 0

Related Questions