How to setup MySQL

Here are the tips I learned while setting up MySQL

  1. # yum install mysql mysql-server
  2. Edit /etc/my.cnf and enter datadir information (i.e /mysql/data)
  3. Bring up /etc/init.d/mysqld start
  4. Start MySQL server
  5. Change the mysql root user password type: mysqladmin -u root password 'newpassword'
  6. Test connecting to mysql Type: mysql -u root -p enter the password when prompted.

Reference

 FullText

To index 3 charater words add the following to the /etc/my.cnf file at the end of the [mysqld] section.

ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

Then restart the mysql service, and run the following sql.

mysql> repair table mw_searchindex quick;

Install and Configure

  1. Edit /etc/my.cnf and enter Data File Location (datadir) information (i.e /mysql/data)
    • [mysqld] datadir=/my
  2. Bring up /etc/init.d/mysqld start
  3. Start MySQL server
  4. Change the mysql root user password type: mysqladmin -u root password 'newpassword'
  5. Test connecting to mysql Type: mysql -u root -p enter the password when prompted.

 Change Root Password

mysqladmin -u root password NEWPASSWORD mysqladmin -u root -p oldpassword newpass

Create Database

The following are the instructions on creating database using mysql client

$ mysql -u adminusername -p

Enter password:Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5340 to server version: 3.23.54
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE databasename;

Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname"-> IDENTIFIED BY "password";

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

mysql> EXIT
Bye

$

NOTE for MySQL 8 you cannot auto create a user when granting.

 

create user 'raw' identified by 'password';
grant select on test_db.* to 'raw';

 phpMyAdmin

  1. Install phpadmin under /opt/www (untar package from /opt/downloads). Rename original phpadmin name to phpMyAdmin.
  2. On Ubuntu type: sudo apt-get install phpmyadmin

SQL Developer

SQL Developer can be installed on Linux or Windows, and now supports not only Oracle but also MySQL and MSSQL

  1. Download SQL Developer from Oracle
  2. To install third party database drivers: start SQL Developer, choose help check for updates, third party

Change Users Password

mysql -u adminusername -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5340 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';

mysql> FLUSH PRIVILEGES;

Backup and Restore

 Setup mySQL backup scripts - Now updated to use mysqli for PHP5 and PHP7

Scripts based on work found here

  1. Create a directory for the mysql backup scripts for example /opt/scripts
  2. Create a directory for the mysql backup files for example /opt/db_backups
  3. Create a file in the backup scripts directory named backup_dbs.sh, set up a cron entry to call this file.
#!/bin/sh
php /opt/scripts/backup_dbs.php
  1. Create a file named backup_dbs_config.php in the scripts directory.
  2. Create another file named backup_dbs.php also in the scripts directory.

 To restore a database type

  1. tar -xvf <database_backup_file>
  2. bunzip2 <database_backup_file>
  3. Login to mysql as root mysql -u root -p
  4. Drop the database (If the destination for restore already exists): mysql> drop database wikidb;
  5. Logout of mysql
  6. Create the databasemysqladmin -uroot -p<passwd>create wikidb
  7. mysql -uroot -p<password> wikidb < wikidb.sql
  8. restore the files from images and uploads directories

mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

If you get the following error is is likely you are using old 16 password hashes.

mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD

The old password hashes are 16 characters, the new ones are 41 characters.  To see what you are using:

Connect to the database, and run the following query:

SELECT user, Length(Password) FROM mysqlmysql.user;

This will show you which passwords are in the old format, ex:

+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| user2    |                 16 |
| user2    |                 16 |
+----------+--------------------+

Notice here that each user can have multiple rows (one for each different host specification).

To update the password for each user, run the following:

UPDATE mysql.user SET Password = PASSWORD('pass') WHERE user = 'user';

UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';

Finally, flush privileges:

Upgrading from MySQL 5.x to 5.5

After upgrading to a new version of MySQL, run mysql_upgrade (see Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”). This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities.

Run the command, using your admin_user and enter the password when prompted.

# mysql_upgrade -u admin_user -p

 

FLUSH PRIVILEGES;

Subject