Tag 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

Backing up a WordPress site

I created this to have something simple to schedule backups of my WordPress site.
It’s tested OK on CentOS 5.5 and 5.6. It creates 2 bzip2 tarballs with timestamps, one for the website and one for the database. And it’s run once a day. It’s then shipped off to another system using Rsync and SSH.

You will need to edit the variables before running it. If you make copies of the script, you can use it to create backups of multiple sites. I hope this will be useful for more than myself.

#!/bin/bash
## DO NOT EDIT
DATE=`date +%d-%m-%Y`
## EDIT BELOW - Configuration parameters

WPSITE=/var/www/html/wordpress
WPSITE_NAME=mywebsite
WPBACKUP=/mnt/wordpress_backup

## STOP EDITING - Configuration parameters

if [ ! -d "$WPBACKUP" ]
then
        mkdir $WPBACKUP
fi

# Grabs the necessary MySQL information from the wordpress site.
DB_NAME=`echo "<?php require_once(\"${WPSITE}/wp-config.php\"); echo DB_NAME;" | php`
DB_USER=`echo "<?php require_once(\"${WPSITE}/wp-config.php\"); echo DB_USER;" | php`
DB_PASSWORD=`echo "<?php require_once(\"${WPSITE}/wp-config.php\"); echo DB_PASSWORD;" | php`
DB_HOST=`echo "<?php require_once(\"${WPSITE}/wp-config.php\"); echo DB_HOST;" | php`

# Dump the database
mysqldump --user=${DB_USER} --password=${DB_PASSWORD} --host=${DB_HOST} $DB_NAME | bzip2 -c > $WPBACKUP/$WPSITE_NAME-db-$DATE.sql.bz2

# Tarballs the database and wordpress files with bzip2
tar -jcvf $WPBACKUP/$WPSITE_NAME-backup-$DATE.tar.bz2 --exclude cache $WPSITE/

// CrashMAG

How to set up RHEL or CentOS 5.5 with Apache, MySQL, PHP 5.3 and WordPress

This is just a short how-to on what I did to set up WordPress on my VPS server. This assumes you’re running RHEL, CentOS or Scientific Linux 5.5.

These instructions assume that you know how and when to use root. Whether it be via sudo or not.

Rather than going with the minimum requirements(*) from WordPress I went with PHP 5.3.

PHP version 4.3 or greater
MySQL version 4.1.2 or greater

Installing MySQL and Apache

1) Install MySQL and Apache

# yum install mysql-server httpd

2) Add MySQL and Apache to the appropriate run levels

# chkconfig --level 345 mysqld on
# chkconfig --level 345 httpd on

3) Adding the PHP 5.3 repository

# rpm -ivh http://repo.webtatic.com/yum/centos/5/`uname -i`/webtatic-release-5-0.noarch.rpm

4) Installing PHP 5.3 via YUM

# yum --enablerepo=webtatic install php php-mysql

Set up MySQL

1) Start MySQL.

# service mysqld start

2) Set the root password.

# mysqladmin -u root password 'your password'

The 5th step is to set up the database and database user for WordPress 

1) Log in to mysql

# mysql -u root -p

Enter your password when prompted.

2) Create the database

> CREATE DATABASE wordpress CHARACTER SET = utf8 COLLATE = utf8_general_ci;

3) Create the database user and assign appropriate rights. In the example the user is called “wp”.

> CREATE USER 'wp'@'localhost' IDENTIFIED BY 'db_passwd';
> GRANT ALL PRIVILEGES ON wordpress.* TO 'wp'@'localhost' IDENTIFIED BY 'db_passwd';
> FLUSH PRIVILEGES;

The 6th step is to download and install WordPress

1) Download

# wget http://wordpress.org/latest.tar.gz

2) Create the wordpress folder.

# mkdir /var/www/html/wordpress

3) Extract

# tar -xzvf latest.tar.gz -C /var/www/html

4) Modify folder ownership.

# chown apache:apache /var/www/html/wordpress -R

-R, –recursive change files and directories recursively

5) Go to http://your site/wordpress and follow the on screen instructions.



// CrashMAG