mysql useful commands

Show the binary log files command

 mysql> SHOW BINARY LOGS;    
 +------------------+------------+  
 | Log_name     | File_size |  
 +------------------+------------+  
 | mysql-bin.000001 | 1074232778 |  
 | mysql-bin.000002 | 1073742234 |  
 | mysql-bin.000003 | 1073743283 |  

To delete binary logs ..001 and 002 run below command
 PURGE BINARY LOGS TO 'mysql-bin.000003';




The MariaDB server's binary log is a set of files containing "events" which represent modifications to the contents of a MariaDB database. These events are written in a binary (i.e. non-human-readable) format. The mysqlbinlog utility is used to view these events in plain text.

Example:-

 mysqlbinlog mariadb-bin.000001 > /tmp/mariadb-bin.sql  

 Change user password:  
 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');  
 Grant privileges to user:  
 CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'paSSword123';  
 mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';  
 ls -1   
 mysql-bin.000051  
 mysql-bin.000052  
 mysql-bin.000053  
 mysql-bin.000054  
 mysql-bin.000055  
 mysql-bin.000056  
 mysql-bin.000057  
 mysql-bin.000058  
  for i in `seq 51 58`;do mysqlbinlog mysql-bin.0000${i} > ${i}-log.sql ; sleep 1 ;done   

#Case Insensitive mysql table name

Open terminal and edit /etc/mysql/my.cnf
sudo nano /etc/mysql/my.cnf
Underneath the [mysqld] section.add:
lower_case_table_names = 1