[SOLVED] mysqldump: unable to backup database

[Adam]

Hi all,

I'm having a problem to backup my database. Error message is shown below. Please advise.

This is the error message:

MariaDB [db_test]> mysqldump db_test > db_test.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > db_test.sql' at line 1
MariaDB [db_test]>

tc@box:~$ mysql -u root -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
tc@box:~$
tc@box:~$
tc@box:~$ mysql -u root -e 'mysqldump db_test > db_test.sql'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > db_test.sql' at line 1
tc@box:~$

Database Info

MariaDB [db_test]> status
--------------
mysql  Ver 15.1 Distrib 10.0.17-MariaDB, for Linux (i686) using readline 5.1

Connection id:          32
Current database:       db_test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less -EM
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.0.17-MariaDB Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Insert id:              1
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Uptime:                 1 hour 38 min 46 sec

Threads: 2  Questions: 187  Slow queries: 0  Opens: 14  Flush tables: 1  Open tables: 4  Queries per second avg: 0.031
--------------

MariaDB [db_test]>

This is how I created the database.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> CREATE DATABASE db_test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> USE db_test
Database changed
MariaDB [db_test]> SHOW TABLES;
Empty set (0.00 sec)

MariaDB [db_test]> CREATE TABLE user (id int(11) not null PRIMARY KEY AUTO_INCREMENT, first varchar(128) not null, last varchar(128) not null, uid varchar(128) not null, pwd varchar(1000) not null );
Query OK, 0 rows affected (0.00 sec)

MariaDB [db_test]>

MariaDB [db_test]> SHOW COLUMNS FROM user;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(11)       | NO   | PRI | NULL    | auto_increment |
| first | varchar(128)  | NO   |     | NULL    |                |
| last  | varchar(128)  | NO   |     | NULL    |                |
| uid   | varchar(128)  | NO   |     | NULL    |                |
| pwd   | varchar(1000) | NO   |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [db_test]>

MariaDB [db_test]> SELECT * FROM user;
Empty set (0.00 sec)

MariaDB [db_test]>

MariaDB [db_test]> INSERT INTO user (id, first , last , uid , pwd) VALUES (null , 'John' , 'Doe' , 'admin' , '123');
Query OK, 1 row affected (0.00 sec)

MariaDB [db_test]> SELECT * FROM user;
+----+-------+------+-------+-----+
| id | first | last | uid   | pwd |
+----+-------+------+-------+-----+
|  1 | John  | Doe  | admin | 123 |
+----+-------+------+-------+-----+
1 row in set (0.00 sec)

MariaDB [db_test]>

[Rich]

Hi Adam

Code: [Select]

tc@box:~$ mysql -u root -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| db_test            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
tc@box:~$
tc@box:~$
tc@box:~$ mysql -u root -e 'mysqldump db_test > db_test.sql'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > db_test.sql' at line 1
tc@box:~$I think the error is telling you one of two things. Either it doesn't recognize the  mysqldump  command or it is unhappy with
your choice of file names. I wonder if it's unhappy about you using the same base name in both instances. Maybe try
something like:

Code: [Select]

mysqldump db_test > db_test2.sql
Also, using code tags is the preferred method of showing commands and screen activity, it preserves spacing and text
alignment.

[Adam]

Hi Adam

I think the error is telling you one of two things. Either it doesn't recognize the  mysqldump  command or it is unhappy with
your choice of file names. I wonder if it's unhappy about you using the same base name in both instances. Maybe try
something like:

Code: [Select]

mysqldump db_test > db_test2.sql

Thanks Rich ... Same error

Code: [Select]

MariaDB [db_test]> mysqldump db_test > db_test2.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > db_test2.sql' at line 1
MariaDB [db_test]>

MariaDB [db_test]> mysqldump db_test > somerandomname.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > somerandomname.sql' at line 1
MariaDB [db_test]>

MariaDB [db_test]> mysqldump db_test > db_test2.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > db_test2.sql' at line 1
MariaDB [db_test]>
MariaDB [db_test]> mysqldump db_test > somerandomname.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > somerandomname.sql' at line 1
MariaDB [db_test]> \q
Bye
tc@box:~$

tc@box:~$ mysql -u root -e 'mysqldump db_test > somerandomname.sql'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysqldump db_test > somerandomname.sql' at line 1
tc@box:~$


Also, using code tags is the preferred method of showing commands and screen activity, it preserves spacing and text
alignment.

Yup, I'm aware of that 
It's just that I can't use color tags within code tags and with quote tag I don't have to scroll at the code

[gerald_clark]

Mysqldump is a program, not a mysql command.
Run it from a shell, not from within mysql.

[Adam]

Mysqldump is a program, not a mysql command.
Run it from a shell, not from within mysql.

I did both ... you can see the screenshot of those in my first and previous post
http://forum.tinycorelinux.net/index.php/topic,20932.msg130759.html#msg130759

[gerald_clark]

I only see you attempting to use mysqldump as a command within the mysql client.
It is not a client command. It is a program fun from the shell.

$ mysqldump db_test > db_test2.sql

[Adam]

I only see you attempting to use mysqldump as a command within the mysql client.
It is not a client command. It is a program fun from the shell.

$ mysqldump db_test > db_test2.sql

Oh, I got it. Thanks Gerald, I appreciate that.
Problem solved