Mysql – Why would I use the MySQL “system” command

command lineMySQL

What can I do with the MySQL client's system command? Why does it exist?

The most productive use I've seen is to look around the file system (e.g., to remember the file name you want to SOURCE or LOAD FILE).

mysql> system ls /tmp
backup.sql 
mysql> source /tmp/backup.sql
Query OK, 1 row affected (0.02 sec)

mysql>

As near as I can tell, you can't pass a query result to the STDIN of a command, you can't pass STDOUT of a command into a MySQL query.. it just doesn't seem widely useful.

Best Answer

There is actually a far more useful application for the SYSTEM command than simply executing ls to see the current directory contents:

As mentioned in the docs (and elaborated on by a comment), this is way to make backups:

echo "FLUSH TABLES WITH READ LOCK; SYSTEM snapshot.sh; UNLOCK TABLES;" | mysql

snapshot.sh contains code to make an atomic snapshot of the mysql tables, using LVM, ZFS or btrfs capabilites. You can't split this up into multiple commands from the shell script, because as soon as the mysql session is closed, the table lock is released.

Related Solutions

MySQL – Fixing mysqladmin Error on Server Running

OMG! I can't believe I solved it!

I started mysqld. However, client could not be started. When I gave the command 'mysql', the following error was shown:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql

Why not create a user account in mysql?! Hence, I created a user account 'root' with 'root' as the password by writing the following in a text file named init-file and placing it where the user mysqlnew have access right. Note that they must be in different lines. Also, ensure that the server is not running.

UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
FLUSH PRIVILEGES;

I started the server by:

./mysqld --basedir=/usr/local/mysqlnew --datadir=/usr/local/mysqlnew/data --query_cache_type=1 --init-file=/home/mysqlnew/init-file &

I then started the client by

mysqlnew@dpaks:/usr/local/mysqlnew/bin$ ./mysql -u root -p
Enter password:

Since I have now created a user account in mysql, I don't need to use --skip-grant-tables anymore for starting server.

However, its not yet finished!

mysql> select 1+1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

So, now I need to rectify this!

mysql> SET PASSWORD = PASSWORD('root');
Query OK, 0 rows affected (0.28 sec)

And that is it! :)

Mysql query not working

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!

Best Answer

Related Solutions

MySQL – Fixing mysqladmin Error on Server Running

Mysql query not working

Related Question