mysql 5.6.28 community server installation

This document is preparing to optimize all MySQL community versions according to your preferences. MySQL Community Edition is a freely downloadable version of the world’s most popular open source database that is supported by an active community of open source developers and enthusiasts.
Create repo file mysql.repo
vi /etc/yum.repo.d/mysql.repo
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Save this file and check repo list
yum repolist
install mysql community server
(yum install mysql-community-server-(version name)
yum install mysql-community-server-5.6.28
 

8 Comments

  1. yum install mariadb-server mariadb
    systemctl start mariadb
    systemctl enable mariadb
    mysql_secure_installation (set password root user)
    Configure MariaDB Master 10.59.60.185
    And add the following lines under [mysqld] section:
    vi /etc/my.cnf
    server_id=1
    log-basename=master
    log-bin
    binlog-format=row
    binlog-do-db=unixmen
    systemctl restart mariadb
    mysql -u root -p
    Create a Slave user and password. For example, we will use sk as Slave username and centos as password:
    MariaDB [(none)]> STOP SLAVE;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO ‘sk’@’%’ IDENTIFIED BY ‘centos’;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [(none)]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [(none)]> SHOW MASTER STATUS;
    +——————–+———-+————–+——————+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————–+———-+————–+——————+
    | mariadb-bin.000001 | 460 | unixmen | |
    +——————–+———-+————–+——————+
    1 row in set (0.00 sec)
    MariaDB [(none)]> exit
    Backup Master server database and transfer it to the Slave
    mysqldump –all-databases –user=root –password –master-data > masterdatabase.sql
    Again login to MySQL as root user:
    mysql -u root -p
    And, unlock the tables:
    MariaDB [(none)]> UNLOCK TABLES;
    Query OK, 0 rows affected (0.00 sec)
    MariaDB [(none)]> quit
    Bye
    copy db backup to slave server
    scp masterdatabase.sql root@10.59.60.186:/root
    ###############################Configure MariaDB Slave
    And add the following entries under [mysqld] section as shown below.
    vi /etc/my.cnf
    server-id = 2
    replicate-do-db=unixmen
    Then, Import the master database using command:
    mysql -u root -p STOP SLAVE;
    Query OK, 0 rows affected (0.01 sec)
    MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST=’10.59.60.185′, MASTER_USER=’sk’, MASTER_PASSWORD=’centos’, MASTER_LOG_FILE=’mariadb-bin.000005′, MASTER_LOG_POS=1818;
    Query OK, 0 rows affected (0.03 sec)
    MariaDB [(none)]> SLAVE START;
    Query OK, 0 rows affected (0.01 sec)
    MariaDB [(none)]> SHOW SLAVE STATUS\G;
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.1.150
    Master_User: sk
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mariadb-bin.000001
    Read_Master_Log_Pos: 460
    Relay_Log_File: mariadb-relay-bin.000002
    Relay_Log_Pos: 531
    Relay_Master_Log_File: mariadb-bin.000001
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB: unixmen
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 460
    Relay_Log_Space: 827
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 1
    1 row in set (0.00 sec)
    Test MariaDB Replication
    mysql -u root -p
    Then, create a database called unixmen, and add some tables and entries in it. Be mindful that the newly created name should be same as in the my.cnf file of both Master and Slave server.
    MariaDB [(none)]> create database unixmen;
    Query OK, 1 row affected (0.00 sec)
    MariaDB [(none)]> use unixmen;
    Database changed
    MariaDB [unixmen]> create table sample (c int);
    Query OK, 0 rows affected (0.01 sec)
    MariaDB [unixmen]> insert into sample (c) values (1);
    Query OK, 1 row affected (0.01 sec)
    MariaDB [unixmen]> select * from sample;
    +——+
    | c |
    +——+
    | 1 |
    +——+
    1 row in set (0.00 sec)
    MariaDB [unixmen]>
    Slave side:
    Now, go to slave server and check the above created entries have been replicated in slave server database.
    Log in to MariaDB prompt as root user:
    mysql -u root -p
    Then, run the following commands to verify whether the entries have been replicated correctly.
    MariaDB [(none)]> use unixmen;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    Database changed
    MariaDB [unixmen]> select * from sample;
    +——+
    | c |
    +——+
    | 1 |
    +——+
    1 row in set (0.00 sec)
    MariaDB [unixmen]>

  2. Skip one error in the replication
    There is the possibility to skip one sql statement and let the replication continue. Please be warned, this can get your database into an inconsistent state if you skip an sql statement from the replication. Make sure you understand why this error happened and that it will not cause inconsistency between your databases if you skip this sql statement.
    If you are sure you want to do this, the first thing to do is to stop the replication process. The second command will set a variable telling the replication process to skip one sql statement before continuing the replication process, and then of course the replication process needs to be started again.
    STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
    START SLAVE;
    SHOW SLAVE STATUS \G

  3. How to take one table backup from one database backup?
    sed -n -e ‘/DROP TABLE.*`table_name`/,/UNLOCK TABLES/p’ funclubs-2019-05-24.sql

  4. ssh -f -L 7600:127.0.0.1:3306 -N -g -i /root/example.pem example.com

    This is command to make ssl tunnel to mysql remove server from localhost

  5. GRANT SELECT,INSERT,UPDATE,CREATE,INDEX,ALTER,EXECUTE ON *.* to root@’yourip’ IDENTIFIED BY ‘yourpassword’;

    This command is use for set permision to access mysqldb.

  6. # script for take multiple database backup
    #!/bin/bash
    #—————————————-
    # OPTIONS
    #—————————————-
    USER=’root’ # MySQL User
    PASSWORD=’Passowrd’ # MySQL Password
    DAYS_TO_KEEP=30 # 0 to keep forever
    GZIP=1 # 1 = Compress
    BACKUP_PATH=’/backup’ # Backup directory location
    #—————————————-

    # Create the backup folder
    if [ ! -d $BACKUP_PATH ]; then
    mkdir -p $BACKUP_PATH
    fi

    # Get list of database names
    databases=`mysql -u $USER -p$PASSWORD -e “SHOW DATABASES;” | tr -d “|” | grep -v Database`
    for db in $databases; do

    if [ $db == ‘information_schema’ ] || [ $db == ‘performance_schema’ ] || [ $db == ‘mysql’ ] || [ $db == ‘sys’ ]; then
    echo “Skipping database: $db”
    continue
    fi

    date=$(date -I)
    if [ “$GZIP” -eq 0 ] ; then
    echo “Backing up database: $db without compression”
    mysqldump -u $USER -p$PASSWORD –databases $db > $BACKUP_PATH/$date-$db.sql
    else
    echo “Backing up database: $db with compression”
    mysqldump -u $USER -p$PASSWORD –databases $db | gzip -c > $BACKUP_PATH/$date-$db.sql.gz
    fi
    done
    # Delete old backups
    if [ “$DAYS_TO_KEEP” -gt 0 ] ; then
    echo “Deleting backups older than $DAYS_TO_KEEP days”
    find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
    fi

Leave a Reply

Your email address will not be published.


*