Category Archives: Databases

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

Set a SQL Server 2005/2008 database offline and disconnect users

These 2 snippets of T-SQL code will disconnect the users (application) and set the database offline. The usefulness of disconnecting the users is so that you’re actually able to set the database as offline.

Note: YOUR_DATABASE will have to be replaced by the name of the database you wish to set as offline.

-- set database offline
alter database YOUR_DATABASE set single_user
with ROLLBACK IMMEDIATE; -- all connections are disconnected
go
alter database YOUR_DATABASE set offline;
go
-- set the database online
alter database YOUR_DATABASE set online; -- database in single-user mode
go
alter database YOUR_DATABASE set multi_user; -- allow multi-user access
go

// 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

How to check for active trace flags on Microsoft SQL Server

You check for active trace flags by running the following query. They may be global or they may be session based.

Checking for active trace flags

DBCC TRACESTATUS

The output could be something like the following.

TraceFlag Status Global Session
--------- ------ ------ -------
4199      1      1      0
4616      1      1      0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If there are no active trace flags you will only see

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Enable session based trace flags

To enable trace flags only in your session use the following two commands.

DBCC TRACEON (trace#)
DBCC TRACEOFF (trace#)

trace#
Is the number of the trace flag to turn on.

Enable trace flags globally

DBCC TRACEON (trace#,-1)
DBCC TRACEOFF (trace#,-1)

-1
Switches on the specified trace flags globally.

To enable a trace flag to persist through a restart. Alter the “Startup Parameters” in the Advanced tab for the SQL Server service in SQL Server Configuration Manager.

One example would be.

Before modification

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

After modification

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T4199

sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]
[-eerror_log_path] [-lmaster_log_path] [-m]
[-n] [-Ttrace#] [-v] [-x] [-gnumber] [-h]

References

DBCC TRACEON (Transact-SQL)
DBCC TRACEOFF (Transact-SQL)
DBCC TRACESTATUS (Transact-SQL)
Trace Flags (Transact-SQL)

// CrashMAG

Resetting the root/postgres password for PostgreSQL

The following is required to reset the root/postgres user password for PostgreSQL. The distribution used in my example is CentOS 5.5 and PostgreSQL 8.4.

Note: By default there’s no password for the postgres user.

In step 2 and 5 you will most likely not be using “ident” but rather “password” or “md5”.

1. Shut down PostgreSQL

# service postgresql stop

2. Reset the authentication mechanism (assuming defaults are already being used)

Edit the /usr/lib/pgsql/data/pg_hba.conf file

# nano /usr/lib/pgsql/data/pg_hba.conf

Navigate down to the line that says

local all all ident

Edit it to

local all postgres trust

And now save the file.

3. Start PostgreSQL

# service postgresql start

4. Log in and change the password

# su - postgres
$ psql -d template1 -U postgres
alter user postgres with password 'new_password';

Or alternatively do it all in one go with the following command

> psql -U postgres template1 -c "alter user postgres with password 'new_password';"

5. Reverse the actions you did in step 2

Edit the /usr/lib/pgsql/data/pg_hba.conf file

# nano /usr/lib/pgsql/data/pg_hba.conf

Navigate down to the line that says

local all all trust

Edit it to

local all postgres ident

And now save the file.

6. Start PostgreSQL

# service postgresql start

Success!

// CrashMAG

Changing the default PostgreSQL data folder (PGDATA)

Installing the PostgreSQL server on RHEL, CentOS, Scientific Linux or Fedora installs the PostgreSQL databases and configuration files in “/var/lib/pgsql/data”.

This may or may not be desirable. Let’s assume for a moment you have a separately crafted partition for PostgreSQL to use, let’s say a RAID10 volume. You’d want to change this.

Change the defaults

Use your favorite text editor, in my case nano to create the following file (must be the same as the name of the service)

# nano /etc/sysconfig/pgsql/postgresql

Add the following

PGDATA=/postgresql/data

Optionally you can also add the following to change the default port (example is the default port)

PGPORT=5432

Adjusting SELinux to permit the new data folder (pgdata) location

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 /opt/postgresql and any other directories/files within it.

# semanage fcontext -a -t postgresql_db_t "/postgresql/data(/.*)?"

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

# restorecon -Rv /postgresql/data

Starting PostgreSQL

# chkconfig --levels 345 postgresql on
# service postgresql initdb
# service postgresql start

You’re all set to go! Keep in mind that PostgreSQL listens to ‘localhost’ by default. To change this you need to alter the “listen_address” parameter in “/var/lib/pgsql/data/postgresql.conf” (change will require restart).

// 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