preloader

Mysql Installation on Linux

Installing MySQL

  • copy tar to folder optional softwares location
  • tar zvfx <downloaded mysql>.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 = ‘<password>’ WHERE User = ‘root’;

update user set authentication_string=PASSWORD(“mysql”) WHERE User = ‘<password>’; (for mysql version above 5.6 the column name is authentication_string instead of password)

GRANT ALL PRIVILEGES on *.* to ‘root’@’localhost’ IDENTIFIED BY ‘<password>’;

FLUSH PRIVILEGES;

Custom DB User And Permissions

Creating User

CREATE USER ‘<db-user>’@’localhost’ IDENTIFIED BY ‘<db-password>’;

Creating Database

CREATE SCHEMA `<db-name>` CHARACTER SET utf8 COLLATE utf8_bin;

Granting Permissions

GRANT ALL PRIVILEGES ON `<db-name>.*` TO ‘<db-user>’@’localhost’; GRANT ALL PRIVILEGES ON `<db-name>`.* TO ‘<db-user>’@’localhost’;

SQL Mode

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’,”));

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”));

Disabling full zero date mode

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,’NO_ZERO_IN_DATE’,”));

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,’NO_ZERO_DATE’,”));

In case need to clear all SQL Mode

SET GLOBAL sql_mode = ”;

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/

Specific Errors

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

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *