Mysql percona DB installation linux

MySQL Percona 5.5 DB installation

1. Go to https://www.percona.com/downloads/Percona-Server-5.5/

and select below options

Version  = "Percona-Server-5.5.28-29.1"

Software = Linux-Generic

And than download the
Percona-Server-5.5.28-rel29.1-334.Linux.x86_64.tar.gz file


2.Pre installation steps :-

create mysql group

$groupadd mysql

create user mysql5

$useradd -g mysql mysql5

change directory to "/home/mysql5"

su - mysql5

$cd /home/mysql5

download or copy the Percona-Server-5.5.28-rel29.1-334.Linux.x86_64.tar.gz  to '/home/mysql5'

untar the file
tar -xvf Percona-Server-5.5.28-rel29.1-334.Linux.x86_64.tar.gz 

Create the directory under '/home/mysql5'

$mkdir mysql

Copy the all untar file to '/home/mysql5/mysql'

Create or copy the my.cnf file in to '/home/mysql5/mysql' and delete or move my.cnf file in /etc

Reference my.cnf file :-

# This is for mysql whihc uses as less memory as possible less than 16 GB of memory

[client]
        port = 3306
        socket = /home/mysql5/tmp/mysql.sock

[mysqld]
        port = 3306
        socket = /home/mysql5/tmp/mysql.sock
        basedir = /home/mysql5/mysql
        datadir = /home/mysql5/data
        user    = mysql5

        skip-external-locking
        default_storage_engine  = InnoDB
        character-set-server    = utf8

        skip-name-resolve
        tmp_table_size=64M
        max_heap_table_size=64M

        connect_timeout=10
        wait_timeout = 3600
        max_connections = 500

        key_buffer_size = 128M
        table_open_cache = 2048
        query_cache_type = 0
        query_cache_limit = 10M
        query_cache_size = 0
        max_allowed_packet = 1024M

        max_connect_errors = 10
        thread_cache_size = 50
        tmpdir = /home/mysql5/tmp

        transaction_isolation = READ-UNCOMMITTED
        slow-query-log
        innodb_thread_concurrency = 0
        innodb_flush_log_at_trx_commit = 2
        innodb_lock_wait_timeout = 50

        innodb_read_io_threads   = 12
        innodb_write_io_threads  = 12
        innodb_buffer_pool_size = 5G
        innodb_log_file_size = 1024M
        innodb_log_files_in_group = 4
        innodb_log_buffer_size = 512M

        #innodb_flushing_avg_loops = 40
        innodb_file_per_table   = OFF
        innodb_stats_on_metadata        = 1
        innodb_old_blocks_pct   = 15
        innodb_old_blocks_time  = 1000
        innodb_buffer_pool_instances    = 1
        innodb_flush_method     = O_DIRECT

        #innodb_print_all_deadlocks      = ON

        innodb_data_home_dir = /home/mysql5/data
        innodb_log_group_home_dir = /home/mysql5/data
        innodb_data_file_path = innodb_data_file_path = ibdata1:10240M;ibdata2:10240M;ibdata3:10240M;ibdata4:10240M;ibdata5:10240M;ibdata6:10240M;extended/ibdata7:10240M;extended/ibdata8:10240M;extended/ibdata9:10240M;extended/ibdata10:10240M;

        slow_query_log_timestamp_always = 1
        log_slow_admin_statements

############# Replication entries #############

        server-id       = 1

[mysqldump]
        quick
        max_allowed_packet = 1024M

[mysql]
        no-auto-rehash

[safe_mysqld]
        err-log = /home/mysql5/data/err.log
        pid-file = /home/mysql5/data/mysqld.pid
        open-files-limit = 4096


#####my.cnf file end####


Create the tmp directory under '/home/mysql5'

$mkdir tmp

Move or copy 'data' directory from '/home/mysql5/mysql' to '/home/mysql5'

$mv /home/mysql5/mysql/data  /home/mysql5

Copy mysql.server  file to  /home/mysql5/mysql/bin

$ cp /home/mysql5/mysql/support-files/mysql.server /home/mysql5/mysql/bin

Edit the mysql.server  file like below :

$cd /home/mysql5/mysql/bin
$vi mysql.server 

basedir=/home/mysql5/mysql
datadir=/home/mysql5/data

MAKE THE CHANGES IN MY.CNF FILE,SUCH AS CHANGE      # IF NECESSARY
   THE PATH OF DATA DIRECTORY

    --> ADD innodb_file_per_table   = 0  ( only for mysql >= 5.6.6 )
    --> ADD explicit_defaults_for_timestamp = TRUE ( To avoid warnings related to timezones. By default time zone table are not updated. )
    --> update old-password = 0 ( backward compatability )
    --> change log-slow-queries to slow-query-log ( as it is deprecated )
    --> add bind-address=0.0.0.0  ( For mysql <= mysql 5.6.6 to avoid bug and  it is fixed in 5.6.7 )
    --> change table_cache to table_open_cache ( as it is deprecated )
    --> add secure-auth=0 ( for mysql >= 5.6.5 this is default on )
    This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format.

OPEN ANOTHER TERMINAL AND CONFIGURE THE FILE LIMITS IN LIMITS.CONF FILE :


$ vi /etc/security/limits.conf

    # Add the Following lines..

        mysql5 soft nofile 100000
        mysql5 hard nofile 100000
        root soft nofile 100000
        root hard nofile 100000
        www soft nofile 100000
        www hard nofile 100000

For Extended Innodbfiles  :

(a)  FROM ROOT USER (in place of /data1 please use custom directory)
   
    $ cd /data1
    $ mkdir mysql5-data
    $ chown mysql5:mysql mysql5-data

(b) FROM MYSQL USER

    $ cd mysql
    $ mv data /home/mysql5
    $ cd data
    $ ln -s /data1/mysql5-data extended

RUN THE MYSQL INSTALL SCRIPT LOCATED IN MYSQL FOLDER

    $ cd mysql
    $ ./scripts/mysql_install_db

Monitor the logs in '/home/mysql5/data'

tail -100f xxxxx.err      

configure mysql auto start when reboot

    # Login as user ROOT

    $ cd /etc/init.d
    $ ln -s /home/mysql5/mysql/bin/mysql.server  mysql5_user_mysql
    $ chkconfig --add mysql5_user_mysql
    $ chkconfig --level 345 mysql5_user_mysql on
    $ chmod 777 /var/lock/subsys/ -R

Set the root password :

$/home/mysql5/mysql/bin/mysqladmin -u root -S /home/mysql5/tmp/mysql.sock password 'yourpassword'

Connect to mysql use (from mysql5 only):

$/home/mysql5/mysql/bin/mysql -uroot  --socket=/home/mysql5/tmp/mysql.sock -pyourpassword -A