Mysql backup script

Question

I am using a script to take a backup of all my databases in the Mysql.

Script is :

#!/bin/bash
cd /root/Desktop/backup
echo “You are In Backup Directory”

Now=$(date +%d-%m-%Y--%H:%M:%S)

File=$Now.sql

mysqldump –u root --all-databases > $File

echo “Your Database Backup Successfully Completed”

But when i m trying to restore the database its giving me the following error and also there is no back at all just a file is created:

SQL query:

Usage: mysqldump [OPTIONS] database [tables]

OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

For more options, use mysqldump --help

MySQL said: Documentation

#1064 - 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 'Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databa' at line 1

Please help.

Onkar Janwa · Accepted Answer · 2014-04-11 10:40:24Z

1

Finally i got it, i need to define password in my.cnf so it automatically used or i have to define it in the command i am executing in the script mysqldump -u root -p 'password' --all-databases > $File.

Thanks for your efforts guys.

edited Apr 11, 2014 at 10:40

Onkar Janwa

3,9603 gold badges34 silver badges47 bronze badges

answered Mar 28, 2014 at 8:10

vic

639 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vic Over a year ago

But still when i m importing it with phpmyadmin,the import command is exectued succuesfully but there is not database tables there in the mysql.Still stucked around.

jasim · Accepted Answer · 2014-03-28 07:18:56Z

0

you will get 1064 error if your delimiter is not properly used or keyword with any argument,check your backup file and try to run that sql command in sqlyog,you can figure out where is the mistake

answered Mar 28, 2014 at 7:18

jasim

2981 gold badge6 silver badges17 bronze badges

Comments

Yogesh Ballani · Accepted Answer · 2014-03-28 07:33:58Z

0

#!/bin/bash 
cd /root/Desktop/backup 
echo "You are In Backup Directory"

Now=$(date +%d-%m-%Y--%H:%M:%S)
File=$Now.sql
mysqldump -uroot -p --all-databases > $File

echo "Your Database Backup Successfully Completed"

You were getting the issue because of "-" it was some other character in your script in mysqldump statement.

answered Mar 28, 2014 at 7:33

Yogesh Ballani

1514 bronze badges

1 Comment

vic Over a year ago

there must be space in between "mysqldump -uroot"

Je Je · Accepted Answer · 2024-06-14 11:20:34Z

This is an old thread, but I got there when looking for a script to dump a mysql table. It seems like an alternative way would now be to use "--login-path" option, having setup a login path (sort of a connection profile) with the command mysql_config_editor.

Configure a login profile ("login-path") in your command line type:

mysql_config_editor set --login-path=login_profile_name --host=localhost --user=myuser --password

you will be prompted for the password of the user

Create a script that calls for the profile ("--login-path=")

#!/bin/bash

# Backup MySQL database
mysqldump --login-path=login_profile_name my_database_name --insert-ignore > path_to_backup_file/backup_file_name.sql

# Compress the backup file (optional)
gzip -f path_to_backup_file/backup_file_name.sql

Make sure that your user has at least PROCESS and LOCK TABLES permissions.

I found this turtorial if you need more information and the mysl doc Just in case this helps someone else...

Collectives™ on Stack Overflow

Mysql backup script

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related