Posts in the “mysql” category

mysqldump command - how to dump (backup) a MySQL database

MySQL backup FAQ - How do I backup/dump a MySQL database schema?

Answer: Use the mysqldump database utility.

MySQL dump examples using the mysqldump utility

On a DOS/Windows pc with no name/password protection, you can dump a database named my_db with the following command, but don't do this just yet:

mysqldump my_db

Note that this gets you not only the database schema, but also the current data in the table.

Show MySQL foreign keys with the "show create table" command

I got a really brutal looking error message from Spring and MySQL yesterday. I've been working on a Java-based web interface for Nagios for a client, and I ran into an error message that basically says "Cannot add or update a child row: a foreign key constraint fails". If you like ugly, gruesome error messages here is the full-blown error:

How I fixed my JDBC MySQL transaction problem

A JDBC MySQL transactions tip: If your transactions aren't working with your new MySQL database you may have the same problem I just had. I created my database tables with the default MySQL storage engine (MyISAM), and guess what? MyISAM doesn't support transactions.

The beginning of a MySQL database script

Here is some code that I use at the beginning of a MySQL database script to (a) create a database, (b) create a local user to access that database ('foo_user'@'localhost'), (c) create a remote user that can access the database ('foo_user'@'%'), and (d) then use that database (which I need to do before starting a bunch of CREATE TABLE statements):

MySQL: Default a field to the current date/time

MySQL date/time FAQ: How do I create a field in a MySQL database table that will default to the current date and time whenever a new record is inserted into my table?

Answer: Just define the field in your table as a timestamp field, and combine that with the default keyword and the MySQL now() function when you define this field in your database table.

The syntax for creating a MySQL timestamp field that defaults to the current date and time when creating a new database table looks like this:

MySQL login FAQ - How do I log in to a MySQL database?

MySQL login FAQ: How do I log into a MySQL database?

Assuming you have the root password, this MySQL command from your Unix/Linux command line will work:

mysql -u root -p

After issuing that MySQL login command you will be prompted for the root user password. Just enter that root password, and you should be in. After you login, your console should look like this (using MySQL version 5):

MySQL CSV import example

I just ran into a situation where I needed to import some CSV data into a MySQL database table. I already had the data I needed in a CSV file format, and I needed to import the data in that file into my MySQL database table.

A few more specifics about the problem and the solution: