MySQL Some Importants Commands In Linux

MySQL User Creation with full permission:

CREATE USER 'dbadmin'@'localhost' IDENTIFIED BY '#Edws4@12';
GRANT ALL PRIVILEGES ON * . * TO 'dbadmin'@'localhost';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO 'reportadmin'@'%' IDENTIFIED BY PASSWORD '#esPeHdK2';

create user reportadmin@'%' identified by '#esPeHdK2';
grant all on *.* to reportadmin@'%' with grant option;


CREATE USER 'dbadmin'@'localhost' IDENTIFIED BY '#P&dhs3@21';
grant all on *.* to dbadmin@'localhost' with grant option;
FLUSH PRIVILEGES;
==============================================

To Change/ update database user permission:
update mysql.user set host='%' where user='dbadmin' and host='localhost';

=============================================

MySQL Remove User Permission :

REVOKE permission ON database.table FROM 'user'@'localhost';

To check MySQL Grant Permission :

SHOW GRANTS FOR 'testuser'@'localhost';

===========================================

Create MySQL User for remote server login:

CREATE USER 'dbadmin'@'%' IDENTIFIED BY 'password';
grant all on *.* to 'dbadmin'@'%' with grant option;
flush privileges;
\q

===============================================

MySQL User Remote Permission with Specific IP :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'114.143.206.66' IDENTIFIED BY 'Wd7$Qts#123' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'27.251.251.81' IDENTIFIED BY 'Wd7$Qts#123' WITH GRANT OPTION;
flush privileges;

=============================================

Create Remote User with Read-Only Permission:

CREATE USER 'b2bsync'@'%' IDENTIFIED BY '$d4GwKX8@121';
grant select on *.* to 'b2bsync'@'%';
flush privileges;
\q


For Specific db name and tables name :

use mysql;

GRANT ALL ON 'DBNAME','tablename' TO 'user@ip';

CREATE USER 'merchantdbuser'@'%' IDENTIFIED BY '$d4GwKX8@121';
grant SELECT,INSERT,UPDATE,DELETE,EXECUTE on *.* to 'merchantdbuser'@'%';
flush privileges;
\q


MySQL User Permission with Single Database:

GRANT ALL PRIVILEGES ON wom_article_management.* To 'dbmaileruser'@'localhost' IDENTIFIED BY 'F4Wmkw8@121$';
==============================================

Delete mysql user:

select user,host from mysql.user;
DROP USER 'merchantdbuser'@'%';

============================================
To change MySQL root Password From MySQL Prompt:

mysql -u root -p'!P4$$#04d#'
mysql -u root -p'My$Ql@123'
SQL User Name: stagesqladmin
SQL Password : #Edws4@12

use mysql;
update user set password=PASSWORD("$e(re@te@123") where User='root';
flush privileges;
\q

============================================

To Reset MySQL root password :

On the command line:

sudo service mysql stop
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot

In MySQL:

use mysql;
update user set Password=PASSWORD('mysql@123') where user='root';
flush privileges;
quit;

service mysqld stop
service mysqld start

======================================


During replication with master slave MySQL:

SELECT user, host FROM mysql.user;
SELECT USER(), CURRENT_USER();
SHOW VARIABLES LIKE 'skip_networking';
show GRANTS for 'replicant'@'%';


If Slave Server has issue with replication, at that situation if we need skip this Error :
Please execute below commands as directed on Slave server.

STOP SLAVE;
set global sql_slave_skip_counter=1;
START SLAVE;
show slave status \G
============================================

Purge mysql bin log file on linux server on Replication Environment:

Note: First check all master and slave server Relay Master log file location should be updated or newer cross check with bin log enabled db server :

Then purge old bin log files:

If you are replicating, then you need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs as those files are necessary for the proper operation of replication. Use following command to purge master logs:


login to mysql prompt:

mysql > PURGE BINARY LOGS TO 'mysql-bin.03';

OR

$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS TO 'mysql-bin.03';"

OR

$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2008-12-15 10:06:06';"

=========================================


View All MySQL user from mysql prompt:
mysql -u root -p

mysql> select user from mysql.user;
mysql> select user, host from mysql.user;

OUTPUT:
mysql> select user, host from mysql.user;
+------------+-----------------------+
| user       | host                  |
+------------+-----------------------+
| root       | 127.0.0.1             |
| root       | ::1                   |
| gitlab     | localhost             |
| mail       | localhost             |
| mydbuser   | localhost             |
| openfire   | localhost             |
| root       | localhost             |
| smail      | localhost             |
| zabbix     | localhost             |
| zabbixuser | localhost             |
| mydbuser   | localhost.localdomain |
+------------+-----------------------+

===================================================

Create MySQL user with all database full privilages for any host:

create user dbadmin@'%' identified by '#Edws4@12';
grant all on *.* to dbadmin@'%' with grant option;
flush privileges;
==================================================
To check MySQL Version:
[root@namdev ~]# mysql -u root -p'mysql@123'

mysql> select version();
=================================================

To Login Remote database server:
# mysql -h '54.179.161.17' -u dbadmin -p    
[54.179.161.17 is remote server ip & dbadmin is remote server user]
=================================================
Check perticular database user permission:

mysql> show grants for dbadmin@'localhost';
=================================================

To check mysql replication:

mysql -u root -p'$e(re@te@123'
Check MySQL replication status:
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

On master database:

# show databases;
# show master status;
SHOW BINARY LOGS;
On Slave databases:
SHOW GLOBAL STATUS like 'slave_running';

STOP SLAVE;
set global sql_slave_skip_counter=1;
START SLAVE;
show slave status \G

=================================

MySQL Commands:

tail -n 100 /var/log/mysqld.log
show processlist;
show status like 'Conn%';
show status like '%onn%';
lsof -i :3306
SHOW GLOBAL STATUS LIKE 'aborted_connects';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'max_used_connections';
SHOW GLOBAL STATUS LIKE "Handler_read%";
SHOW GLOBAL STATUS;
mysql> STATUS;
=====================================

MySQL Backup Script in Bash / Cron Job

crontab -e

# ....
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command

 15 2 * * * mysqldump -u mysqluser -pmysqlpassword db_name | gzip > /var/backups/mysql/db_name_`date '+\%Y\%m\%d-\%H\%M\%S'`.sql.gz
 20 2 * * * find /var/backups/mysql/db_name* -mtime +30 -exec rm {} \;

======================================

To Check MySQL Connection :

vim conn.php

<?php
$link = mysql_connect('54.169.69.182', 'fc_backenduser', 'f!4$7c4Y@123');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>