Skip to main content

Posts

MySQL Enterprise Incremental Backup Python | MEB Incremental Bckup Python | mysqlbackup

 MySQL Enterprise Incremental Backup: ## Script Name : MEB_IncreBkup.py ## Script Purpose: Execute incremental backup of the MySQL Enterprise, compress it, check status, if script fail send notification, and email backup log # Import python libraries import os import time import datetime import pipes import socket host = socket . gethostname () date = time . strftime ( '%Y%b%d-%H%M%S' ) date1 = time . strftime ( '%Y-%m-%d.%H-%M-%S' ) incremental = "--incremental --incremental-base=history:last_backup" backupdir = "--backup-dir=/tmp/backup" + date backupimage = "--backup-image=/mysql/NFS" + host + "....
Post a Comment
Read more

MySQL InnoDB performance | MySQL InnoDB Stats

  InnoDB Stats Parameters: InnoDB stats parameter controls the indexed column’s index statistics       persistent to disk or     not persistence to disk There are two options available to control them,  Globally and  locally at table level. This parameter needs to be tune according to the nature of the application, DML activities against the database and tables, for stable execution plan of the query or for better performance of the query. There are two options are available to store index statistics persistent to disk or non-persistent to disk. Following parameter available for MySQL InnodB stats, starting from MySQL 8. In older version of MySQL such 5.6 and 5.7 innodb_stats_sample_pages was used. Starting form MySQL 8.0 it is deprecated and we have to use innodb_stats_persistent_sample_pages Ref.: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_auto_recalc •    innodb_stats_auto_...
Post a Comment
Read more

MySQL Enterprise Full Backup - MEB Full Backup Python script

MySQL Enterprise Backup - MEB - Python script: ## Script Name : bkup.py ## Script Purpose: Execute full backup of the MySQL Enterprise, compress it, check backup job status, email backup log, if backup fail send notification # Import python libraries import os import time import datetime import pipes import socket host = socket.gethostname() date = time.strftime('%Y%b%d-%H%M%S') date1 = time.strftime('%Y-%m-%d.%H-%M-%S') backupdir="--backup-dir=/tmp/backup" + date backupimage="--backup-image=/mysql/<NFS>/"+host+".mts.backup.mbi_" + date image="backup-to-image --compress" logfile="/tmp/backup"+date+...
Post a Comment
Read more

MEB - MySQL Enterprise Backup

MEB - MySQL Enterprise Backup: Backup using backup command: #/bin/mysqlbackup \ --user=<id> \ --password=<code> \ --socket=/mysql/mysql.sock \ --backup-dir=/mysql/mysql-backup-dir-`date +'%F_%H-%M-%S'` backup it will create directory /mysql/mysql-backup-dir-`date +'%F_%H-%M-%S'` --> backup the database in directory /datadir which resides inside the /mysql/mysql-backup-dir.... Backup and apply log: Add following connection id, code, and socket in /etc/my.cnf file [mysqlbackup] host=localhost user=<id> password=<code> socket=/mysql/mysql.sock #/bin/mysqlbackup \ --backup-dir=/mysql/mysql-backup-dir-`date +'%F_%H-%M-%S'` backup-and-apply-log Command will create directory /mysql/mysql-backup-dir... --> backup the database --> Restore database in datadir which is inside /mysql/mysql-backup-dir Note: Backups created with the --skip-unused-pages option cannot be restored using copy-back-and-apply-log. Ref. https://dev.mysql.com/do...
Post a Comment
Read more

Install MariaDB Community 10.5

Install MariaDB MySQL Community Edition: Update repository: Add following in file /etc/yum.repos.d/MariaDB.repo # MariaDB 10.5 RedHat repository list - created 2020-12-21 23:35 UTC # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.5/rhel7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 Install MariaDB # yum install MariaDB-server MariaDB-client Enable MariaDB to start from boot # systemctl enable mariadb Start MariaDB # systemctl start mariadb Set root password Execute following program to set root user password and secure mysql installation # mysql_secure_installation
Post a Comment
Read more

Migrate MySQL User from MySQL 5.6 to 8.0.21

Migrate MySQL User from MySQL 5.6 to 8.0.21: If you are migrating MySQL from 5.6 to 8.0.21 new env. (Not in place upgrade) then this is one of the  option to migrate mysql users.  $ mysql -uroot -p -S/mysql/mysql.sock --skip-column-names --execute \ "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | tee step1 $ mysql -uroot -p -S/mysql/mtsprd/data/mtsprd.sock  --skip-column-names < step1 | tee step2 $ sed -e "s/ IDENTIFIED BY PASSWORD '.*'//" -e "s/$/;/" < step2 | tee step3 Note: Interesting use of function CONCAT, QUOTE, Linux utility tee, and sed makes it easy to generate script.  Generate dump file from MySQL 5.6.30: $ mysqldump -uroot -p --set-gtid-purged=OFF -S /mysql/mysql.sock --databases <db1> \<db2> --events --triggers --routines --force --ignore-table=<table1> \ --ignore-table=<table2> > /mysql/dump.sql Note:  --force, -f - Ignore all errors; ...
Post a Comment
Read more

Python Script to connect MySQL Database on Linux

Python Script to connect MySQL Database on Linux : I explored two options to connect MySQL database from Python script: 01. mysql.connector - Explore more about mysql.connector / Python 02, MySQLdb module - Explore more about MySQLdb mysql.cnnector: Download mysql.connector from MySQL Community Download Install mysql.connector on Linux: Using pip: # pip install mysql-connector-python Using yum: # yum update mysql-community-release # yum install mysql-connector-python Explore mysql.connector connection examples  
Post a Comment
Read more