MySQL backup FAQ - How do I backup/dump a MySQL database schema?
Answer: Use the mysqldump database 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.
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:
Here's a quick example of how to default a MySQL timestamp field to "now", i.e., the current date and time. The following MySQL table definition shows a field named date_created that uses the now function to record the timestamp for when the row was created:
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):
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.
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:
I got the following MySQL error ("1005 error") earlier today when trying to create a table with a foreign key relationship:
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 FAQ - how do I list all the databases in a MySQL database?
Simple. From the mysql command line, use this command:
show databases;
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:
