mysql

recent posts related to the mysql database

MySQL "show users" - how to show/list the users in 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:

A MySQL database backup (mysqldump) shell script

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.

The PHP MySQL mysql_connect() can't connect to local MySQL server through socket error

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 - Incorrect table definition (TIMESTAMP)

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.

A MySQL foreign keys “drop table” and re-create table example

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:

Update MySQL timestamp on update

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:

A MySQL Vacuum database tables script (auto_increment reset)

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:

The MySQL default port is ...

Today I was configuring a MySQL JNDI connection pool on a Glassfish server, when my brain went completely blank, and I couldn't remember what port MySQL listens on by default.

After a few moments of research, including looking at some output from netstat, I found the answer ... the MySQL default port is 3306.

3306

 

Initial MySQL root user account

After finishing my latest Drupal installation, I installed a MySQL database on my Linux server using yum, and then my mind went blank -- I couldn't remember how to create the initial MySQL “root” user (i.e., the initial MySQL user account).

Still unable to remember how to do this, I went ahead and started the MySQL server from my Linux root prompt just to make sure it was installed okay, like this: