AIX: MySQL Packages installation

The purpose of this installation is to install the minimum of packages. Maybe some functionality are missing, and it might be add on the same way as below.

First download MySQL packages from Pware

Download the latest version of the following packages:

pware61.mysql-dev.5.1.51.0.bff.gz
pware61.mysql.5.1.51.0.bff.gz
pware61.zlib.1.2.5.0.bff.gz

Uncompress the packages and extract files in /tmp

[root@labotest:/tmp/]# ls
pware61.mysql-dev.5.1.51.0.bff.gz
pware61.mysql.5.1.51.0.bff.gz
pware61.zlib.1.2.5.0.bff.gz
[root@labotest:/tmp/]# gzip -d *.gz
[root@labotest:/tmp/]# restore -xqvf pware61.mysql-dev.5.1.51.0.bff
[root@labotest:/tmp/]# restore -xqvf pware61.mysql.5.1.51.0.bff
[root@labotest:/tmp/]# restore -xqvf pware61.zlib.1.2.5.0.bff

Create a new directory or filesystem to store the mysql binaries (or create /opt/pware), and copy ./opt/pware inside:

[root@labotest:/tmp/]# cd opt/pware
[root@labotest:/tmp/opt/pware/]# mkdir /opt/mysql
[root@labotest:/tmp/opt/pware/]# cp -Rhp * /opt/mysql

Create a symbolic link to /opt/pware if it's not the mysql binary directory

[root@labotest:/opt/]# ln -s mysql pware

Create a user and group to start mysql DB (mysql/mysql)

Export LIBPATH, add it into your .profile

[root@labotest:/etc/]# export LIBPATH=/opt/mysql/lib/mysql

Add start/stop script into your prefered start/stop files, add also at beginning of the script: LIBPATH=/opt/mysql/lib/mysql

You can add an entry into /etc/inittab and /etc/rc.shutdown: /opt/mysql/share/mysql/mysql.server start or stop
Or /etc/rc.d/rc2.d, with Sxxmysql for start and Kxxmysql to stop

AIX: MySQL DB initialization

Copy a sample configuration file, and adapt to your environment (password,user,datadir…):

[root@labotest:/opt/mysql/share/mysql/]# cp -p my-medium.cnf /etc/my.cnf
[root@labotest:/etc/]# cat my.cnf | grep -v "^#"
[client]
password        = mysql
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
user=mysql
datadir=/TEST
log-bin=mysql-bin
binlog_format=mixed
server-id       = 1

[safe_mysqld]
err-log=/var/log/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Initialize the mysql database

[root@labotest:/opt/mysql/bin/]# ./mysql_install_db  --user=root
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/opt/pware/bin/mysqladmin -u root password 'new-password'
/opt/pware/bin/mysqladmin -u root -h labotest password 'new-password'

Alternatively you can run:
/opt/pware/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /opt/pware ; /opt/pware/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /opt/pware/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /opt/pware/bin/mysqlbug script!

Start the MySQL server:

[root@labotest:/opt/mysql/]# /opt/mysql/share/mysql/mysql.server start
Starting MySQL
. SUCCESS!

Initialize the root password for the DB:

# /opt/mysql/bin/mysqladmin -u root password 'new-password'
# /opt/mysql/bin/mysqladmin -u root -h labotest password 'new-password'

Now you can test a connexion:

[root@labotest:/etc/]# /opt/mysql/bin/mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql> use mysql ;
Database changed
mysql> select * from user;
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
| labotest     | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
| 127.0.0.1 | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost |      |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
| labotest     |      |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
5 rows in set (0.00 sec)

mysql> quit

Stop MySQL server:

[root@labotest:/opt/mysql/]# ./share/mysql/mysql.server stop 
Shutting down MySQL
... SUCCESS!

AIX: MySQL password recovery procedure

First stop MySQL and restart it in safe mode:

[root@labotest:/opt/mysql/bin/]# /opt/mysql/share/mysql/mysql.server stop
Shutting down MySQL
..110615 16:49:40 mysqld_safe mysqld from pid file /TEST/labotest.pid ended
 SUCCESS!                       
[root@labotest:/opt/mysql/bin/]# ./mysqld_safe --skip-grant-tables &
[1]     360472
110615 16:50:11 mysqld_safe Logging to '/TEST/labotest.err'.
110615 16:50:11 mysqld_safe Starting mysqld daemon with databases from /TEST
[root@labotest:/etc/]# /opt/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.51-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql> use mysql ;
Database changed
mysql> select * from user;
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
| labotest     | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
| 127.0.0.1 | root |          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost |      |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
| labotest     |      |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
5 rows in set (0.00 sec)

mysql> update user set password=PASSWORD("mysql") where User='root';
Query OK, 3 rows affected (0.10 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Now you are able to stop MySQL and start in normal mode

MySQL Database Backup/Restore

The syntax for backing up a MySQL database is as follows:

 mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]

The syntax for restoring a MySQL database is:

 mysql -u [username] -p[password] [database] < [backup_file]

MySQL delete a database

Drop a DB: Connect to the DB mysql, and then drop the DB that you want to remove:

[root@aix:/root]# mysql --user=root --password mysql
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
| tsmmonitor         | 
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database tsmmonitor;
Query OK, 12 rows affected (0.30 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| test               | 
+--------------------+
3 rows in set (0.00 sec)
mysql> quit

MySQL update a user password

Update tom user password

[root@aix:/root]# mysql --user=root --password mysql
Enter password: 
mysql> use mysql;

mysql> update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';

MySQL error

ERROR 13

/usr/sbin/mysqld: File './mysql-bin.index' not found (Errcode: 13)

chown -R mysql:mysql /path/to/DB # Set your Owner/Group
chmod -R 755 /path/to/DB # Set your permissions

Make sure to specify the following in your my.cnf

user = mysql # Use the file owner to access the files
mysqld = /usr/sbin/mysqld_safe

Then start mysql like this:

mysqld_safe --defaults-file=/path/to/my.cnf &

Table of Contents