Mysql Installation Reference

Installing MySQL

 * copy tar to folder optional softwares location
 * tar zvfx .tar.gz
 * go to script folder and run mysqlinstalldb
 * Follow the instruction on the screen carefully

User permissions
Needed to add a user for mysql so that the process can run with this user.
 * groupadd mysql
 * useradd -r -g mysql mysql
 * cd mysql
 * chown -R mysql.
 * chgrp -R mysql.

Using User permissions
When running mysqlinstalldb with the user name created above use:

scripts/mysql_install_db --user=mysql

Make sure to run mysqld as mysql user:

sudo -u mysql ./mysqld_safe &

Configuring root password
sudo service mysql stop

sudo mkdir -p /var/run/mysqld

sudo chown mysql:mysql /var/run/mysqld

sudo mysqld_safe --skip-grant-tables &

sudo mysql (This should take to the MySql Prompt)

UPDATE mysql.user SET password = ' ' WHERE User = 'root';

update user set authentication_string=PASSWORD("mysql") WHERE User = ' '; (for mysql version above 5.6 the column name is authentication_string instead of password)

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY ' ';

FLUSH PRIVILEGES;

Creating User
CREATE USER ''@'localhost' IDENTIFIED BY '';

Creating Database
CREATE SCHEMA `` CHARACTER SET utf8 COLLATE utf8_bin;

Granting Permissions
GRANT ALL PRIVILEGES ON `.*` TO ''@'localhost'; GRANT ALL PRIVILEGES ON ``.* TO ''@'localhost';

To Check MYSQL mode
SHOW VARIABLES LIKE 'sql_mode';

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

In case of of failure due to ONLY_FULL_GROUP_BY and NO_ZERO_IN_DATE need to clear both these

Disabling full group-by mode
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',&apos;&apos;));

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',&apos;&apos;));

Disabling full zero date mode
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_IN_DATE',&apos;&apos;));

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_ZERO_DATE',&apos;&apos;));

In case need to clear all SQL Mode
SET GLOBAL sql_mode = &apos;&apos;;

To clear SQL mode from configuration
Change file /etc/mysql/mysql.conf.d/mysqld.cnf

Paste below line on [mysqld] portion

sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Original complete SQL Mode

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Remember to restart mysql

sudo service mysql restart

Enabling Logging
For logging to be enabled for all general logging MySql queries create a file under : /etc/my.cnf In this file add the below contents:

[mysqld] general_log_file        = /opt/mysql/logs/mysql.log general_log             = 1 expire_logs_days        = 10 max_binlog_size         = 100M Restart mysqld to enable logging.

The log will be written to the file mysql.log under the above location. Ensure that this file exisits and also that the user mysql has permission to write to this file.

To disable logging set the log value to zero general_log             = 0 Restart mysqld. Further, different logs can be enabled; more info available here.

Error Notes
In case of errors on shared object:

Set the path to the .so files in the lib folder (system so library files)

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib/:/usr/lib/

libaio
libaio.so.1: cannot open shared object file: No such file or directory

Run the below command:

sudo apt-get install libaio1 libaio-dev

libnuma
libnuma.so.1: cannot open shared object file: No such file or directory

Run the below command:

sudo apt-get install libnuma-dev