
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
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]>
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
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
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
Cialis 10mg Filmtabletten cialis sildenafil levitra Acheter Amoxil Pharmacie Propecia Gastrite
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.
sed -n -e ‘/CREATE TABLE.*`tables_name`/,/CREATE TABLE/p’ db_backup.sql > tables_name.sql
# 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