Category Archives: MySQL

MySQL and PostgreSQL rosetta stone

This is a short table of useful and common MySQL & PostgreSQL commands put up against each other.

MySQL

PostgreSQL

Command line client mysql psql
Connect to database use mysql; \connect postgresql;
List databases show databases; \l
List tables show tables; \dt
Describe table describe table; \d table;
Show server version select version(); select version();
Show the current time select now(); select now();
Authentication GRANT ALL PRIVILEGES ON *.* TO ‘monty’@’localhost’; (http://dev.mysql.com/doc/refman/5.5/en/adding-users.html) Edit pg_hba.conf (http://wiki.postgresql.org/wiki/Client_Authentication)
Check user privileges SHOW GRANTS FOR ‘root’@’localhost’; \du (List all users and their permission levels)
Backup mysqldump [options] db_name [tbl_name …] pg_dump [option…] [dbname]

Note for future: This reference list will grow.

References

http://dev.mysql.com/doc/index.html
http://www.postgresql.org/docs/manuals/

Notice

Table shows correctly in Mozilla Firefox but it does not in Google Chrome or any other Webkit browser for that matter. Go figure.

// CrashMAG

Change the default MySQL data directory with SELinux enabled

This is a short article that explains how you change the default MySQL data directory and adjust SELinux to account for the changes. The article assumes that you’re running either RHEL, CentOS, Scientific Linux or Fedora with SELinux enabled. This works with the most recent EL (6.2) version.

We’ll be doing this in the following order.

  • Stopping the MySQL server
  • Create a new data directory and move the content from the old data directory
  • Correct the MySQL configuration file
  • Adjust SELinux parameters to accept our new change
  • Starting the MySQL server

Stopping the MySQL server

# service mysqld stop

Create a new data diretory and move the content from the old one

Creating a new data directory

# mkdir /srv/mysql/
# chown mysql:mysql /srv/mysql

Moving the original data files

 # mv /var/lib/mysql/* /srv/mysql/

Correct the MySQL configuration file

Edit the my.cnf file for your distribution. In my example it’s located in the /etc/mysql/ directory. RHEL/CentOS/Scientific Linux put the my.cnf file directly in /etc by default.

# nano /etc/mysql/my.cnf

Change

datadir=/var/lib/mysql

to

datadir=/srv/mysql

and

socket=/var/lib/mysql/mysql.sock

to

socket=/srv/mysql/mysql.sock

and save the file.

Adjust SELinux parameters to accept our new change

Should the following command output “Permissive” or “Disabled” then you may skip the details for SELinux.

# getenforce

Run the semanage command to add a context mapping for /srv/mysql.

# semanage fcontext -a -t mysqld_db_t "/srv/mysql(/.*)?"

Now use the restorecon command to apply this context mapping to the running system.

# restorecon -Rv /srv/mysql

Starting the MySQL server

# service mysqld start

Verifying access and connectivity

$ mysql -u root -p
mysql> show databases;

If this is working, you’re up and running. Should you get a message that says

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

then add the following to your /etc/my.cnf

[client]
socket = /srv/mysql/mysql.sock

Optionally you can just use

$ mysql -u root -p --protocol tcp

to avoid connecting via the socket.

// CrashMAG

List of useful MySQL commands

Logging in

$ mysql -h hostname -u root -p

If you omit the password value following the –password or -p option on the command line, mysql prompts for one.

Example of password typed in clear text on the commandline

$ mysql -h hostname -u root -psecret

Create a MySQL database

mysql> create database [database_name];

List all databases on the MySQL server

mysql> show databases;

Use a database

mysql> use [db_name];

The USE db_name statement tells MySQL to use the db_name database as
the default (current) database for subsequent statements. The database
remains the default until the end of the session or another USE
statement is issued.

See all the tables in a database

mysql> show tables;

See database field formats

mysql> describe [table name];

DESCRIBE provides information about the columns in a table. It is a
shortcut for SHOW COLUMNS FROM. These statements also display
information for views.

Deleting a database

mysql> drop database [database name];

DROP DATABASE drops all tables in the database and deletes the
database.

Deleting a table

mysql> drop table [table_name];

DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed.

Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a

mysql> SELECT * FROM [table name] WHERE [column] RLIKE "^a";

RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

Change a users password

$ mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Delete a user

mysql> DROP USER 'jeffrey'@'localhost';

The statement removes privilege rows for the account from all grant tables.

View privileges/grants/rights for a user

show grants for 'user'@'localhost';

Set a root password if there is on root password

# mysqladmin -u root password newpassword

Update database permissions/privileges.

mysql> flush privileges;

Delete a column

mysql> alter table [table name] drop column [column name];

Add a new column to db

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name

mysql> alter table [table name] change column old_col_name new_col_name;

// CrashMAG

Resetting the root password for MySQL running on RHEL or CentOS

I recently had to reset the MySQL root password due to the fact that initializing it the way I assumed it should did not work. The following procedure will work in CentOS/RHEL/Scientific Linux and Fedora.

After installing MySQL using

# yum install mysql-server

I ran the command

# mysqladmin -u root password 'new-password'

Trying to log in with the following failed

# mysql -u root -p

with the following error

Access denied for user 'root'@'localhost'

Decided to not spend more time as it’s a fresh MySQL installation. And did the following to reset the root password for MySQL.

Resetting the root password

1) Stopped the MySQL service.

# service mysqld stop

2) Started MySQL in safe mode.

# mysqld_safe --skip-grant-tables &

3) Logged in using root.

# mysql -u root

4) Reset the password.

> use mysql;
> update user set password=PASSWORD("mynewpassword") where User='root';
> flush privileges;
> quit

5) Stop MySQL in safe mode.

# service mysqld stop

6) Start MySQL.

# service mysqld start

7) Log in using the new password.

# mysql -u root -p

Success!

// CrashMAG