MYSQL ADMINISTRATIVE COMMANDS
MYSQL ADMINISTRATIVE COMMANDS
Introduction
MySQL is a popular tool for storing and managing data on websites. It's free and easy to work with, helping websites run smoothly by organizing information effectively. Using a language called SQL, MySQL makes it simple to add, retrieve, and modify data in databases.
Installation
Check whether MySQL has been installed before or not if it is not installed install MySQL using the below link
To install MySQL in Centos7:
Reference:https://www.linode.com/docs/databases/mysql/how-to-install-mysql-on-centos-7/
To Create a Database:
To list all the databases:
To use the desired Database:
To Create Table use:
How to insert data into the table:
To describe the table :
The description about the author table will be displayed.
To display all the rows i.e., data stored in the table:
To insert a Column in the table:
Example:
ALTER TABLE testtable ADD last_name varchar (20) AFTER first_name;
Modify the Column in Table using Alter Table command statement in mysql:
Syntax:
ALTER TABLE table_name ALTER COLUMN column_name column_type;
To drop a row by using a id value:
To drop a column in a Table:
Syntax:
ALTER TABLE table_name DROP COLUMN column_name
Practicing outside the mysql in terminal command line:
To check the my sql server is running:
To check the mysql version:
mysqladmin -u root -ptmppassword status
To check the status of all mysql server variable’s and values’s:
To check all the processlist in the mysql:
Multiple process at the same time:
To connect Mysql remote server:
mysqladmin -h <ipaddress> -u root -p status
To start and stop the mysql replication on a slave server.
mysqladmin -u root -p start-slave
mysqladmin -u root -p stop-slave
To Store a mysql server debug information to logs:
mysqladmin -u root -p debug
Enter password:
Able to grant privileges, flush previleges etc are in it,
And also kill the processlist unwanted in it.
BACKUP COMMANDS IN MYSQL:
TO Backup MySQL db
Mysqldump [options] [database_name] > [backup_name]
Practice:
database named Rakesh I have created and mount in the external hard drive /mnt/backups/ .then the basic command to run that backup is,
Syntax :
Sudo mysqldump –p Rakesh > /mnt/backups/Rakesh.sql
-p: is the password for MySQL
If necessary, Back up all the databases:
mysqldump –u USER –p --all-databases > /mnt/backups/all_databases.sql
Compressing your Backup:
mysqldump -u USER -p -C Rakesh > /mnt/backups/Rakesh.sql.tgz
For compressed file :
Dumping:
mysqldump db_name table_name | gzip > table_name.sql.gz
Restore it
gunzip < table_name.sql.gz | mysql -u username -p db_name
Restore it.
MYSQL flush commands:
flush-hosts: Flush all host information from host cache.
flush-tables: Flush all tables.
flush-threads: Flush all threads cache.
flush-logs: Flush all information logs.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.
mysqladmin -u root -p flush-hosts
mysqladmin -u root -p flush-tables
mysqladmin -u root -p flush-threads
mysqladmin -u root -p flush-logs
mysqladmin -u root -p flush-privileges
mysqladmin -u root -p flush-status
To view mysql admin options use
Mysqladmin --help
