mysql 5.6.28 community server installation

10 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]>

   Reply

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

   Reply

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

   Reply

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

   Reply

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.

   Reply

6. sed -n -e ‘/CREATE TABLE.*`tables_name`/,/CREATE TABLE/p’ db_backup.sql > tables_name.sql

   Reply

7. # 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

   Reply

8. mysql -u root -p -e “select ip,name FROM zabbix.interface INNER JOIN zabbix.hosts ON zabbix.interface.hostid = zabbix.hosts.hostid” >> /zabbix.txt

   Reply

9. select name from hosts where name like ‘Template%’;

   Reply

10. mysql -u root -p -e “select hostid,ip from zabbix.interface” >> /zabbix_hostid_ip.txt;
    mysql -u root -p -e “select ip,name FROM zabbix.interface INNER JOIN zabbix.hosts ON zabbix.interface.hostid = zabbix.hosts.hostid” >> /zabbix_ip_name.txt;
    mysql -u root -p -e “select hostid,templateid from hosts_templates” >> /zabbix_hostid_templateid.txt;
    mysql -u root -p -e “select hostid,host from hosts” >> /zabbix_hostid_hostname;

    Reply