mysql

MySQL “create database” FAQ: Can you share an example of how to create a MySQL database, including (a) creating the MySQL database and (b) a new MySQL database user, with all appropriate MySQL grant permissions for that user?

Sure. I've written about the process of How to add/create a MySQL user before, but I just ran across the Drupal installation documentation, which also has a nice description for how to create a MySQL database (for use with Drupal), so I thought I'd include some of their documentation here, along with some notes from my own recent Drupal and MySQL installation.

1) Create a new MySQL database

The first part of the Drupal installation document refers to creating a MySQL database:

MySQL users FAQ: How do I show/list MySQL users, i.e., the user accounts in a MySQL database?

To show/list the users in a MySQL database, first log into your MySQL server as an administrative user using the mysql client, then run this MySQL query:

MySQL database backup FAQ: Can you share a Linux shell script that I can use to make a MySQL backup (i.e., a shell script that wraps the mysqldump command)?

I currently have a collection of websites on several different servers (including GoDaddy and A2 Hosting web servers), so I was just spending some time trying to automate my MySQL database backups. To that end, I just created a MySQL shell script that I use on each Linux server to make my database backups, and I thought I'd share that script here.

PHP MySQL socket error FAQ: I'm getting a MySQL error like "Warning: mysql_connect(): Can't connect to local MySQL server through socket '/home/lib/mysql/mysql.sock'" when trying to connect to my MySQL database from a PHP application, what can I do to fix this?

I just ran into this problem on a new server I'm using with A2 Hosting, where the exact error I got in my PHP MySQL program is this:

MySQL Error 1293 FAQ: Help, I'm getting a MySQL incorrect table definition error message related to a TIMESTAMP column. How do I get past it?

If you ever get this MySQL Error 1293 (like I just did):

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

fear not, it may not be too bad. Let's take a look.

MySQL FAQ: How can I drop a series of database tables that have foreign key relationships between them?

If you ever have a problem where you need to drop a series of MySQL database tables that have foreign key relationships between them, the key to doing this is setting the FOREIGN_KEY_CHECKS variable before and after your MySQL DROP TABLE queries.

For example, something like this should work:

MySQL timestamp FAQ: How can I update a MySQL TIMESTAMP field when I issue an update for a MySQL database table?

MySQL has some crazy rules about how timestamp fields can be created, but one nice "timestamp update" syntax you can use looks like this, assuming that you're declaring a timestamp field named last_updated:

MySQL vacuum FAQ: How do I vacuum a MySQL database table, or an entire MySQL database?

This may be an unusual situation, but as I'm creating a new application that uses a MySQL database, I've found that I want a "MySQL vacuum" operation; I want to reset all my MySQL auto_increment counters back to 1 without having to completely drop and then re-create my MySQL database.

Specifically what I'm doing is this:

MySQL FAQ: How do I define a MySQL auto increment field?

Here's an example of the way I normally create a MySQL auto increment (auto_increment) field:

MySQL foreign key FAQ: How do I define a foreign key in MySQL?

Answer: Here's a quick example of how I typically define a foreign key in MySQL.

Diving right into an example, here's the definition for a MySQL database table named nodes that I will link to from a second table: