====== 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 [[http://pware.hvcc.edu/|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 &