The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:
PURGE BINARY LOGS TO ‘binlogname’;
PURGE BINARY LOGS BEFORE ‘datetimestamp’;
These will clear all binary logs before the binlog or timestamp you just specified. For example, if you login to mysql run
mysql> PURGE BINARY LOGS TO ‘mysql-bin.000223’;
this will erase all binary logs before ‘mysql-bin.000223’.
If you run
mysql> PURGE BINARY LOGS BEFORE DATE(NOW() – INTERVAL 3 DAY) + INTERVAL 0 SECOND;
this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;
then add this to /etc/my.cnf
Show all MySQL users:
mysql> SELECT user FROM mysql.user;
List only unique user names:
mysql> SELECT DISTINCT user FROM mysql.user;
Show MySQL users and hosts they are allowed to connect from:
mysql> SELECT user,host FROM mysql.user;
Show MySQL users, their passwords and hosts:
mysql> SELECT user,host,password FROM mysql.user;
in MySQL 5.7 and higher:
mysql> SELECT host,user,authentication_string FROM mysql.user;
Show privileges granted to the current MySQL user:
mysql> SHOW GRANTS;
Show privileges granted to the MySQL user (if you don’t specify a host for the user name, MySQL assumes % as the host):
mysql> SHOW GRANTS FOR 'user_name';
Show privileges granted to a particular MySQL user account from a given host:
mysql> SHOW GRANTS FOR 'user_name'@'host';
– e.g. –
mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> SHOW GRANTS FOR 'root'@'%';
mysql> SHOW GRANTS FOR 'admin'@'192.168.0.1';
Step 1 – Prerequisites
Login to your Debian 9 system using shell access. For remote systems connect with SSH. Windows users can use Putty or other alternatives applications for SSH connection.
Run below commands to upgrade the current packages to the latest version.
sudo apt update
sudo apt upgrade
Step 2 – Configure MySQL PPA
MySQL team provides official MySQL PPA for Debian Linux. You can download and install the package on your Debian system, which will add PPA file to your system. Run below command to enable PPA.
sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb
During the installation of MySQL apt config package, It will prompt to select MySQL version to install. Select the MySQL 5.7 or 5.6 option to install on your system. Continue reading “Installing MySQL on Debian” »
Debian stopped packaging mysql-client as of buster. You can use
apt-get install default-mysql-client
Which will install mariadb-client-10.3. MariaDB is a fork of MySQL. The client can still be started with the command mysql.
If you want to execute logical backups using an alternative to mysqldump that works with parallel threads giving faster execution times mydumper is the correct tool.
In order to use mydumper and myloader you can use the following sample commands:
For dumping/exporting database:
mydumper -u <username> -p <password> -B <database name> -d /path/to/dump
For importing database (for percona or master-master replication):
myloader -u <username> -p <password> -B <database name> -d /dump/path --enable-binlog
Continue reading “Install Mydumper Myloader on Centos 7” »
Service mysql stop is being ignored. How to fix?
mysqladmin -u root -p shutdown
Alternatively, In case you have started
So in this case you should stop bootstrapped node using
Exporting percona database is however a bit tricky way to make funcitonal. The usual-
mysqldump -u root -p [database] > database.sql
will stuck and shoot you many error. All you need is the add an extended parameters to execute the dump, and here it is:
mysqldump -u root -p [database] lock-for-backup > database.sql
And that should do the trick!
First of all, why we choose three nodes and not only two? In any cluster, the number of nodes should be odd, so in the case of disconnection of a node, we assume that the highest group of servers has the fresh data, and should be replicated to the down node to avoid data loss. This is related only to resolve conflicts in data replication, we won’t loose data written only to the disconnected node.
This is used to avoid a circumstance called split brain, in which we can’t automatically choose which node has correct data. Think for example of a 2 node cluster where both nodes are disconnected from each other, and the same record is written to both nodes: who wins when they come back online? We don’t know, so split brain happens, and we have to manually decide wich record is the right one.
The number of nodes that is needed to determine wich part of the cluster has the right data is called QUORUM, in our case, the quorum will be 2. So we need 2 servers always be connected to each other. In case all three nodes will go down, we have a split brain and we must decide wich server should go in bootstrap mode manually, this is the procedure to determine wich will be the main server to resume from the split brain.
Configuring Percona XtraDB Cluster on Debian 8
This tutorial describes how to install and configure three Percona XtraDB Cluster nodes on Debian 8 servers, we will be using the packages from the Percona repositories.
IP address: 192.168.152.100
IP address: 192.168.152.110
IP address: 192.168.152.120
On each host, modify file /etc/hosts as follows to ensure DNS will work correctly. Continue reading “Install Percona XtraDB Cluster for MySQL 5.7 on Debian 8” »
Few small hack will ease you importing large sized mysql dump in a bit faster process. The trick worked for me for a 70 GB dumped sql, can’t guarantee it’d work for you!
need to change the following:
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
Why these settings ? Continue reading “MySQL my.cnf tweak for faster importing large database” »