Mysql database basic user guide

MySQL Database –Basic User Guide

1. MySQL Database –Basic
User Guide

2. MySQL Architecture -Physical Structure

3. Configuration files:
auto.cnf:Containsserver_uuid
my.cnf:MySQLConfiguration file.
MySQL Log files:There few important log files provided by MySQL to trace and store various types of database related operations and activities.
Error log:Also called as ‘MySQL Server Logs’. By Default enable and default location is data directory. MySQL server/Error Log file forlogging server activities like startup-shutdown
events, crash-recovery events, connection errors etc.MySQLserver log file to record mysqldserver activities as NOTE, WARNING, ERROR on the pre-defined severity basis.
Variable: log_error
log-error = mysqld.log
General Log: By Default this log is disable, we can enable and disable it dynamically without downtime.Tolog client queries. Enabled it when you want to trouble shoot queries
executed from client to see what client sending to mysqldserver.
Variables:
general_log
general_log_file
Slow Query log:ByDefault this log is disable, we can enable and disable it dynamically without downtime.
The slow query log contain SQL query with additional info that took more than specified long_query_time(in seconds) to execute. Threshold time in seconds for a query execution, to
mark that as slow query.
Variables:
slow_query_log
slow_query_log_file
Binary log:Alsocalled as ‘binlog’, itcontainsevents for database related changes like table creation and data changes.
By Default this log is disable, we cannot enable and disable it dynamically and requires downtime.
This log is very useful for Point in Time recovery as well as for Replication, it is mandatory to binlogenabled on MySQL server if you want to useMySQLReplication.
Binary file has binary format, to see the contents in this file we can usemysqlbinlogutilitywhich will convert this file in text format and same can be used for Point in time restore to
apply incremental changes.

4. Miscellaneous files:basedir=dir_nameDirectorylocationtothe MySQL binaries
installation.
datadir=dir_name
Directory location to the MySQLdatabasedata, status and log files.
pid-file=file_name
Filenameinwhich the mysqldserver should write its process IDinformation.
socket=file_name, -S file_name
On Unix, the name of the Unix socket file to use, for connections made using a
named pipe to a local server

5. MySQL Architecture-MySQL Logical Structure

7. Storage
Engines
MyISAM
InnoDB
MERGE
MEMORY (HEAP)
ARCHIVE
CSV
FEDERATED

8. MyISAM
MyISAMextends the former ISAM storage engine. The MyISAMtables are optimized for compression and speed. MyISAMtables are also portable between platforms and
operating systems.
The size of the MyISAMtable can be up to 256TB, which is huge. In addition, MyISAMtables can be compressed into read-only tables to save spaces. At startup, MySQL checks
MyISAMtables for corruption and even repairs them in a case of errors. The MyISAMtables are not transaction-safe.
Before MySQL version 5.5, MyISAMis the default storage engine when you create a table without specifying the storage engine explicitly. From version 5.5, MySQLuses InnoDB
as the default storage engine.
InnoDB
The InnoDBtables fully support ACID-compliant and transactions. They are also optimal for performance. InnoDBtable supports foreign keys, commit, rollback, roll-forward
operations. The size of an InnoDBtable can be up to 64TB.
Like MyISAM, the InnoDBtables are portable between different platforms and operating systems. MySQL also checks and repairs InnoDBtables
MERGE
A MERGE table is a virtual table that combines multiple MyISAMtables that have a similar structure to one table. The MERGE storage engine is also known as the MRG_MyISAM
engine. The MERGE table does not have its own indexes; it uses indexes of the component tables instead.
Using MERGE table, you can speed up performance when joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the
MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.
Memory
The memory tables are stored in memory and use hash indexes so that they are faster than MyISAMtables. The lifetime of the data of the memory tables depends on the
uptime of the database server. The memory storage engine is formerly known as HEAP.
Archive
The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine
compresses a record when it is inserted and decompress it using the zliblibrary as it is read.
The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support indexes, so it is required a full table scanning for reading rows.
, if necessary, at startup.

9. MERGE
A MERGE table is a virtual table that combines multiple MyISAMtables that have a similar structure
to one table. The MERGE storage engine is also known as the MRG_MyISAMengine. The MERGE
table does not have its own indexes; it uses indexes of the component tables instead.
Using MERGE table, you can speed up performance when joining multiple tables. MySQL only allows
you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use
DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying
tables will not be affected.
Memory
The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM
tables. The lifetime of the data of the memory tables depends on the uptime of the database server.
The memory storage engine is formerly known as HEAP.
Archive
The archive storage engine allows you to store a large number of records, which for archiving
purpose, into a compressed format to save disk space. The archive storage engine compresses a
record when it is inserted and decompress it using the zliblibrary as it is read.
The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support
indexes, so it is required a full table scanning for reading rows.

10. CSV
The CSV storage engine stores data in comma-separated values (CSV)
file format. A CSV table brings a convenient way to migrate data into
non-SQL applications such as spreadsheet software.
CSV table does not support NULL data type. In addition, the read
operation requires a full table scan.
FEDERATED
The FEDERATED storage engine allows you to manage data from a
remote MySQL server without using the cluster or replication
technology. The local federated table stores no data. When you query
data from a local federated table, the data is pulled automatically from
the remote federated tables.

11. •Storage engine:
MySQL component that manages physical data (file management) and locations. Storage engine responsible for SQL statement execution
and fetching data from data files. Use as aplugin and can load/unload from running MySQLserver. Few of them as following,
•InnoDB:
•Fully transactional ACID.
•Offers REDO and UNDO for transactions.
•Data storage in tablespace:
•–Multiple data files
–Logical object structure using InnoDBdata and log buffer
•Row-level locking.
•NDB (For MySQL Cluster):
•Fully Transactional and ACID Storage engine.
•Distribution execution of data and using multiple mysqld.
•NDB use logical data with own buffer for each NDB engine.
•Offers REDO and UNDO for transactions.
•Row-level locking.
•MyISAM:
•Non-transactional storage engine
•Speed for read
•Data storage in files and use key, metadata and query cache

12. •–FRMfor table structure
–MYI for table index
–MYD for table data
•Table-level locking.
•MEMORY:
•Non-transactional storage engine
•All data stored in memory other than table metadata and structure.
•Table-level locking.
•ARCHIVE:
•Non-transactional storage engine,
•Store large amounts of compressed and unindexed data.
•Allow INSERT, REPLACE, and SELECT, but not DELETE or UPDATE sqloperations.
•Table-level locking.
•CSV:
•Stores data in flat files using comma-separated values format.
•Table structure need be created within MySQL server (.frm)
•

13. mysql> select * from information_schema.engines;
OR
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAMtables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAMstorage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------------------------
Change default setting of Engine in inifile parameter
[mysqld]
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
Or
Command line during starting:
--default-storage-engine=InnoDB

14. mysql> select * from engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB| YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAMtables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM| DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

15. SQL Execution
https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-
and-components/

16. Start MySQL Server
Start MySQL Server on Linux
On Linux, you can start the server with the following commands using service, init.d, and systemd.
Start MySQL Server using service
sudoservice mysqlstart
Start MySQL Server using usinginit.d
sudo/etc/init.d/mysqlstart
Start MySQL Server using systemd
sudosystemctlstart mysqld
Start MySQL Server on Windows
On Windows, you can start the MySQL Server using the mysqldprogram as follows:
First, open the Run dialog by pressing Windows+Rkeyboards:
Second, type cmdand press Enter:
Third, type mysqldand press Enter:
mysqld
If the bin folder is not in the Windows path environment, you can navigate to the bin folder e.g., C:\Program
Files\MySQL\MySQL Server 8.0\bin\and use the mysqldcommand.

17. Stop MySQL Server
Stop MySQL Server on Windows
On Windows, you can stop MySQL Server using the program mysqladmin.
The program mysqladminlocates in the folder <path_to_installation_dir>\bin, where path_to_installation_diris the path to the
installation directory e.g., C:\Program Files\MySQL\MySQL Server 8.0\
Typically, you should add the pathname of the MySQL bin directory to Windows path environment variable to access any MySQL
programs in the bin directory faster.
To stop MySQL, you follow these steps:
First, launch the Command Prompt by pressing Windows+Rto open the Run box and type cmdand press Enter.
Second, navigate to the bin folder of the MySQL if it is not in the Window path environment.
Third, use the following command to stop MySQL Server:
mysqladmin-u root -p shutdown
Enter password: ********
It prompts for a password of the root account. You need to enter the password and press the Enter keyboard.
The program will stop the MySQL Server.

18. Stop MySQL Server
Stop MySQL Server on Linux
To stop MySQL Server on Linux, you use the following command:
/etc/init.d/mysqldstop
Some Linux distributions provide server command:
service mysqldstop
Or
service mysqlstop
Restart MySQL Server
You use the following command to restart the MySQL server On Linux:
service mysqlrestart
If the name is MySQL service is mysqldnot mysql, you need to change the service name in the command
as shown in the following command:
service mysqldrestart
Or you can use the init.dto start the MySQL service:
/etc/init.d/mysqldrestart

19. User Management
CREATE USER [IF NOT EXISTS] account_nameIDENTIFIED BY 'password';
mysql-u root -p
Enter password: ********
mysql> create user bob@localhostidentified by 'Secure1pass!';
mysql-u bob -p
Enter password: ********
mysql> show databases;
use bobdb
mysql> select user from mysql.user;
mysql> grant all privileges on bobdb.* to bob@localhost;
mysql> show tables;
SHOW GRANTS FOR super@localhost;
SHOW GRANTS;
HOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

20. SELECT current_role();
mysql> select user, host from mysql.user;
mysql> drop user dbadmin@localhost
RENAME USER old_user1 TO new_user;
SELECT host, user FROM mysql.userWHERE user = 'doe' and host = 'localhost';
RENAME USER fxTO fc;
USE mysql;
Change MySQL user password using UPDATE statement
UPDATE user
SET authentication_string= PASSWORD('dolphin’) WHERE user = 'dbadmin' AND host = 'localhost';
FLUSH PRIVILEGES;
Change MySQL user password using the SET PASSWORD statement
SET PASSWORD FOR 'dbadmin'@'localhost' = PASSWORD('bigshark');
SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark;
Change MySQL user password using ALTER USER statement
ALTER USER dbadmin@localhostIDENTIFIED BY 'littlewhale';

21. DB Login & list of Database
C:\Program Files\MySQL\MySQL Workbench 8.0 CE>mysql-u dmapuser-h 198.148.1.110 -P 3306 dmap-p
Enter password: *************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.17 MySQL Community Server -GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dmap |
| information_schema|
| mysql |
| performance_schema|
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)

22. SELECT user(); --Current user
SELECT current_user();
SELECT user, host, db, command FROM information_schema.processlist;
SELECT user FROM mysql.userGROUP BY user;
SHOW FULL TABLES;
mysql> use globalmap
Database changed
mysql> show tables;
+------------------------------+
| Tables_in_globalmap |
+------------------------------+
| code_object_validation|
| column_constraint_validation|
| data_object_validation|
| db_details_main |
| db_error_master |
| db_migration_plan|
| db_object |
| db_object_error |
| db_object_type_validation|
| db_objects_type |
| parameter_execution|
| parameter_value_store|
| user_detail |

23. Backup
mysqldump-u dmapuser-h 192.168.1.11 -P 3306 dmap-p > C:\databasebackup\MySQL_Backup\dmap_backup_mysql_on2410.sql
C:\Program Files\MySQL\MySQL Workbench 8.0 CE>mysqldump-u dmapuser-h 192.168.1.11 -P 3306 dmap-p >
C:\databasebackup\MySQL_Backup\dmap_backup_mysql_on2410.sql
Enter password: *************
C:\Program Files\MySQL\MySQL Workbench 8.0 CE>
mysqldump--databases database_onedatabase_two> two_databases.sql
mysqldump--all-databases > all_databases.sql
shell> mysqldump--databases db1 db2 db3 > dump.sql
Tables Backup
mysqldump-u{backup_user} -p{backup_password} from_db_nametable_to_backup> backup_file.sql
mysqldump-u mkyong-p wrdp1 wp_postmeta> wp_postmeta.sql
mysqldump-u dmapuser-h 192.168.1.11 -P 3306 dmap-p code_object_validation> C:\databasebackup\MySQL_Backup\code_object.sql
Restoring a Backup
mysqldatabase_name< database_name.sql
To dump only specific tables from a database, name them on the command line following the database name:
shell> mysqldumptest t1 t3 t7 > dump.sql

24. Backup
Back a table ‘wp_postmeta’ to a SQL file ‘wp_postmeta.sql’
$ mysqldump-u mkyong-p wrdp1 wp_postmeta> wp_postmeta.sql
Enter password:
# backup + gzip
$ mysqldump-u mkyong-p wrdp1 wp_postmeta| gzip> wp_postmeta.sql
#multiple tables -wp_postmetaand wp_posts
$ mysqldump-u mkyong-p wrdp1 wp_postmetawp_posts> tables.sql

25. MySQL Export Table to CSV
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled'
INTO OUTFILE 'C:/tmp/cancelled_orders.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
Exporting data with column headings
(SELECT 'Order Number','OrderDate','Status')
UNION
(SELECT orderNumber,orderDate, status FROM orders INTO OUTFILE 'C:/tmp/orders.csv’ FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '“’ LINES TERMINATED
BY '\r\n');
Handling NULL values
SELECT orderNumber, orderDate, IFNULL(shippedDate, 'N/A’) FROM orders INTO OUTFILE 'C:/tmp/orders2.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"' LINES
TERMINATED BY '\r\n';

26. Import CSV File Into MySQL Table
CREATE TABLE discounts (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
expired_dateDATE NOT NULL,
amount DECIMAL(10 , 2 ) NULL,
PRIMARY KEY (id)
);
LOAD DATA INFILE 'c:/tmp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

27. Transforming data while importing
Sometimes the format of the data does not match the target columns in the table. In simple cases, you can
transform it by using the SET clause in the LOAD DATA INFILE statement.
Suppose the expired date column in the discount_2.csv file is in mm/dd/yyyyformat.
LOAD DATA INFILE 'c:/tmp/discounts_2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@expired_date,amount)
SET expired_date= STR_TO_DATE(@expired_date, '%m/%d/%Y');

28. Basic Scripts-1
1. Access monitor:mysql-u [username] -p;(will prompt for password)
2. Show all databases:show databases; --The SHOW SCHEMAS command is a synonym for SHOW DATABASES, therefore the following
command returns the same result
3. SHOW DATABASES LIKE '%schema’;
4. SELECT schema_name FROM information_schema.schemata;
5. Access database:mysql-u [username] -p [database](will prompt for password)
6. Create new database:create database [database];
7. Select database:use [database];
8. Determine what database is in use:select database();
9. Show all tables:show tables; The SHOW TABLES command allows you to show if a table is a base table or a view. To include the table type in
the result, you use the following form of the SHOW TABLES statement.
10. SHOW FULL TABLES
11. SHOW TABLES LIKE 'p%’;
12. SHOW TABLES LIKE '%es’;
13. SHOW FULL TABLES WHERE table_type= 'VIEW’;
14. SHOW TABLES FROM mysqlLIKE 'time%’;
15. SHOW TABLES IN mysqlLIKE 'time%';
16. Show table structure:describe [table];
17. List all indexes on a table:show index from [table];
18. Create new table with columns:CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);
19. Adding a column:ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);
20. Adding a column with an unique, auto-incrementing ID:ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT
PRIMARY KEY;
21. Inserting a record:INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

29. Basic Scripts-2
1. MySQL function for datetime input:NOW()
2. Selecting records:SELECT * FROM [table];
3. Explain records:EXPLAIN SELECT * FROM [table];
4. Selecting parts of records:SELECT [column], [another-column] FROM [table];
5. Counting records:SELECT COUNT([column]) FROM [table];
6. Counting and selecting grouped records:SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY
[column];
7. Selecting specific records:SELECT * FROM [table] WHERE [column] = [value];(Selectors:<,>,!=; combine multiple selectors
withAND,OR)
8. Select records containing[value]:SELECT * FROM [table] WHERE [column] LIKE '%[value]%';
9. Select records starting with[value]:SELECT * FROM [table] WHERE [column] LIKE '[value]%';
10. Select records starting withvaland ending withue:SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';
11. Select a range:SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];
12. Select with custom order and only limit:SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT
[value];(Order:DESC,ASC)
13. Updating records:UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];
14. Deleting records:DELETE FROM [table] WHERE [column] = [value];
15. Deleteall recordsfrom a table (without dropping the table itself):DELETE FROM [table];(This also resets the incrementing
counter for auto generated columns like an id column.)
16. Delete all records in a table:truncate table [table];
17. Removing table columns:ALTER TABLE [table] DROP COLUMN [column];
18. Deleting tables:DROP TABLE [table];
19. Deleting databases:DROP DATABASE [database];
20. Custom column output names:SELECT [column] AS [custom-column] FROM [table];
21. Export a database dump (more infohere):mysqldump-u [username] -p [database] > db_backup.sql
22. Use--lock-tables=falseoption for locked tables (more infohere).
23. Import a database dump (more infohere):mysql-u [username] -p -h localhost [database] < db_backup.sql
24. Logout:exit;
25.

30. Show current logged users
SELECT user, host, db, command FROM information_schema.processlist;
+-------+----------------+---------------+---------+
| user | host | db | command |
+-------+-----------------+---------------+---------+
| local | localhost:50591 | classicmodels| Sleep |
| root | localhost:50557 | NULL | Query |
+-------+-----------------+---------------+---------+
2 rows in set (0.00 sec)
MySQL SHOW PROCESSLIST
mysql>SHOW PROCESSLIST;
+----+-----------------+-----------------+---------------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+---------------+---------+------+------------------------+------------------+
| 4 | event_scheduler| localhost | NULL | Daemon | 2246 | Waiting on empty queue | NULL |
| 14 | root | localhost:50924 | NULL | Query | 0 | starting | SHOW PROCESSLIST |
| 15 | car | localhost:50933 | classicmodels| Sleep | 2 | | NULL |
+----+-----------------+-----------------+---------------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec
DESCRIBE orders;
SHOW COLUMNS FROM table_name;
the following statement lists all columns of the payments table in the classicmodelsdatabase.
SHOW FULL COLUMNS FROM payments \G;
SHOW COLUMNS FROM table_nameLIKE pattern;
SHOW COLUMNS FROM table_nameWHERE expression;

31. show authors -displays names, locations and authors
show binlogevents -see replication
show binary logs -see replication
show character set
show collation
show columns -information about table fields
show contributors -displays names, locations and contributors
show create [database|event|function|
procedure|schema|trigger|view] -display how the object was created
show databases
show [engine|engines]
show errors
show table status
show storage engines
show tables
show triggers
show variables
show warnings
show processlist-display the currently running processes in MySQL
show profile -this is a newer command and queries the profiling that can
be done on a session-level basis
show schemas -same as show databases
show events -dislaysdatabase events including the time the event
happened
show full tables -same as show tables but with additional column that
specifies whether the table is a base table, view or system view
show function code -displays the ordinal position and instructions for
each step in a stored function
show function status -displays all functions associated with all
databases
show grants
show [index|indexes]
show keys
show master status -see replication
show open tables -display all open tables
show plugins -displays all plugins including storage engine plugins
show privileges -display the name, context and comment about each
type of privilege that can be used in a grant statement
show procedure code
show procedure status
show slave host -see replication
show slave status -see replication
show status -displays the status of the system a good command to start
with
The showextension can obtain metadata information from the information_schemadatabase,

32. Table Maintenance
•check table-used to determine if a table is corrupted (options are extended, meduim, changed, fast, quick, for
upgrade)
•repair table-used to repair a table (options are quick, extended use_frm)
•checksum table-obtain a checksum for the table (options are quick, extended), see above for checksum's on tables
•analyze table-updates the statistics of a table to help the optimizer
•optimize table-resolves data and index fragmentation
MySQL provides several useful statements that allow you to maintain database tables effectively. Those statements
enable you to analyze, optimize, check, and repair database tables.
Analyze table statement
ANALYZE TABLE payments;
Optimize table statement
OPTIMIZE TABLE table_name;
Check table statement
CHECK TABLE table_name;
Repair the table statement
REPAIR TABLE table_name;

33. Backup -Using the mysqldumptool
1) Using the mysqldumptool to make a backup of a single database
mysqldump--user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <database_name>
2) Using the mysqldumptool to make a backup of multiple databases
To make a backup of multiple databases, you specify a list of the database names after the --database option:
mysqldump--user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <dbname1>[,<dbname2>, ...]
mysqldump--user=root --password=Supe!rPass1 --result-file=c:\backup\classicmodels_world.sql --databases classicmodelsworld
3) Using the mysqldumptool to make a backup of all databases
To make a backup of all databases in a MySQL Server, you use the –all-database option:
mysqldump--user=<username> --password=<password> --result-file=<path_to_backup_file> --all-databases
4) Using the mysqldumptool to make a backup of specific tables from a database
To make a backup of specific tables from a database, you use the following command:
mysqldump--user=<username> --password=<password> --result-file=<path_to_backup_file> <database_name> <table_name>
5) Using a mysqldumptool to make a backup of database structure only
To make a backup of the database structure only, you use the --no-data option:
mysqldump--user=<username> --password=<password> --result-file=<path_to_backup_file> --no-data --databases <database_name>
6) Using the mysqldumptool to make a backup of data only
To make a backup of the database data only, you use the --no-create-info option:
mysqldump--user=<username> --password=<password> --result-file=<path_to_backup_file> –-no-create-info --databases
<database_name>

34. MySQL Restore: Restoring from a Dump File
Use the source command to load data into the MySQL Server:
First, connect to MySQL server:
C:\>mysql -u root -p
Enter password: ********
Second, drop the mydbdatabase:
mysql>drop database mydb;
Third, use the SOURCE command to load the dump file:
mysql>source c:\backup\mydb.sql
mysql> source c:\temp\mysqlsampledatabase.sql)
mysql> show databases;

35. MySQL CREATE TABLE statement examples
Let’s take some examples of creating new tables.
1) MySQL CREATE TABLE simple example
The following statement creates a new table named tasks:
CREATE TABLE IF NOT EXISTS tasks (
task_idINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
start_dateDATE,
due_dateDATE,
status TINYINT NOT NULL,
priority TINYINT NOT NULL,
description TEXT,
created_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS checklists (
todo_idINT AUTO_INCREMENT,
task_idINT,
todoVARCHAR(255) NOT NULL,
is_completedBOOLEAN NOT NULL DEFAULT
FALSE,
PRIMARY KEY (todo_id, task_id),
FOREIGN KEY (task_id)
REFERENCES tasks (task_id)
ON UPDATE RESTRICT ON DELETE CASCADE
);

37. Numeric Types Description
TINYINT A very small integer
SMALLINT A small integer
MEDIUMINT A medium-sized integer
INT A standard integer
BIGINT A large integer
DECIMAL A fixed-point number
FLOAT
A single-precision
floating point number
DOUBLE
A double-precision
floating point number
BIT A bit field
String Types Description
CHAR
A fixed-length nonbinary (character)
string
VARCHAR A variable-length non-binary string
BINARY A fixed-length binary string
VARBINARY A variable-length binary string
TINYBLOB A very small BLOB (binary large object)
BLOB A small BLOB
MEDIUMBLOB A medium-sized BLOB
LONGBLOB A large BLOB
TINYTEXT A very small non-binary string
TEXT A small non-binary string
MEDIUMTEXT A medium-sized non-binary string
LONGTEXT A large non-binary string
ENUM
An enumeration; each column value
may be assigned one enumeration
member
SET
A set; each column value may be
assigned zero or more SET members
Date and Time Types Description
DATE A date value in CCYY-MM-DD format
TIME A time value in hh:mm:ss format
DATETIME A date and time value inCCYY-MM-DD hh:mm:ssformat
TIMESTAMP A timestamp value in CCYY-MM-DD hh:mm:ss format
YEAR A year value in CCYY or YY format

38. Performance Tuning
events_statements_current |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name|
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name|
| events_statements_summary_by_thread_by_event_name|
| events_statements_summary_by_user_by_event_name|
| events_statements_summary_global_by_event_name
mysql> select * from events_statements_summary_by_digestlimit 1\G

39. SELECT ess.user, ess.host, (a.total_connections-a.current_connections) -ess.count_staras not_closed
, ((a.total_connections-a.current_connections) -ess.count_star) * 100 /
(a.total_connections-a.current_connections) as pct_not_closed
FROM performance_schema.events_statements_summary_by_account_by_event_name ess
JOIN performance_schema.accountsa on (ess.user= a.userand ess.host= a.host)
WHERE ess.event_name= 'statement/com/quit’ AND (a.total_connections-a.current_connections) > ess.count_star
;
+-----------+---------------+------------+----------------+
| user | host | not_closed| pct_not_closed|
+-----------+---------------+------------+----------------+
| applicat| 10.0.246.74 | 31 | 0.0001 |
| applicat| 10.0.246.73 | 59 | 0.0003 |
| replicate | 10.0.246.72 | 1 | 100.0000 |
| applicat| 10.0.246.76 | 4 | 0.0024 |
| root | localhost | 3 | 0.0053 |
| applicat| localhost | 51880 | 0.2991 |
| applicat| 10.0.246.77 | 1 | 100.0000 |
+-----------+---------------+------------+----------------+
https://fromdual.com/mysql-performance-schema-hints
Accounts not properly closing connections

40. Unused indexes
SELECT object_schema, object_name, index_nameFROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_nameIS NOT NULL AND index_name!= 'PRIMARY'
AND count_star= 0 AND object_schemaNOT IN ('mysql', 'performance_schema')
ORDER BY object_schema, object_name;
+---------------+------------------+------------+
| object_schema| object_name| index_name|
+---------------+------------------+------------+
| applicat| access | name |
| applicat| access | owner |
| applicat| access | source |
| applicat| access | password |
| applicat| access | type_2 |
| applicat| access | type_3 |
| applicat| active_customers| scustomer|
| applicat| addresses | domain |
| applicat| addresses | dtype|
...
| applicat| timegroups| name |
| applicat| transactions | customer |
| applicat| unlimited_access| name |
| applicat| urls | name |
| applicat| vouchers | batch |
| applicat| voucher_batches| customer |
+---------------+------------------+------------+
279 rows in set (2.43 sec)

41. Who created temporary (disk) tables
Works since 5.6
SELECT user, host, event_name, count_starAS cnt
, sum_created_tmp_disk_tablesAS tmp_disk_tables, sum_created_tmp_tablesAS tmp_tables
FROM performance_schema.events_statements_summary_by_account_by_event_name
WHERE sum_created_tmp_disk_tables> 0
OR sum_created_tmp_tables> 0
;
+----------+---------------+---------------------------------+------------+-----------------+------------+
| user | host | event_name | cnt| tmp_disk_tables| tmp_tables|
+----------+---------------+---------------------------------+------------+-----------------+------------+
| applicat| 10.0.246.74 | statement/sql/select | 565349929 | 0 | 10293241 |
| applicat| 10.0.246.74 | statement/sql/show_fields| 8 | 8 | 8 |
| applicat| 10.0.246.73 | statement/sql/select | 571206486 | 0 | 10429993 |
| applicat| 10.0.246.73 | statement/sql/show_databases| 2 | 0 | 2 |
| applicat| 10.0.246.76 | statement/sql/select | 17814977 | 0 | 2696 |
| applicat| 10.0.246.76 | statement/sql/show_databases| 7 | 0 | 7 |
| applicat| 10.0.246.76 | statement/sql/show_tables| 10 | 0 | 10 |
| applicat| localhost | statement/sql/select | 1856221142 | 828 | 4142585 |
| applicat| localhost | statement/sql/show_databases| 7 | 0 | 7 |
| applicat| localhost | statement/sql/show_tables| 7 | 0 | 7 |
| applicat| localhost | statement/sql/update_multi| 14 | 2 | 3 |
+----------+---------------+---------------------------------+------------+-----------------+------------+
28 rows in set (0.02 sec)

42. Storage Engines per schema
For defining backup strategy, preparing migration to InnoDBor Galera Cluster for MySQL, etc.
Works since 5.5 (5.1?)
SELECT table_schemaAS `schema`, engine, COUNT(*) AS `tables` , ROUND(SUM(data_length)/1024/1024, 0) AS data_mb,
ROUND(SUM(index_length)/1024/1024, 0) index_mb
FROM information_schema.tablesWHERE table_schemaNOT IN ('mysql', 'information_schema', 'performance_schema')
AND engine IS NOT NULL GROUP BY table_schema, engine
;
+---------------------+--------+--------+---------+----------+
| schema | engine | tables | data_mb| index_mb|
+---------------------+--------+--------+---------+----------+
| mantis | MyISAM| 31 | 0 | 0 |
| mpm | InnoDB| 3 | 0 | 0 |
| mysql_sequences| InnoDB| 2 | 0 | 0 |
| mysql_sequences| MEMORY | 1 | 0 | 0 |
| otrs | InnoDB| 73 | 13 | 4 |
| quartz | InnoDB| 12 | 0 | 0 |
| tracking | MyISAM| 1 | 0 | 0 |
+---------------------+--------+--------+---------+----------+

43. Tablespace related Configuration and operations:
MySQL InnoDBConfiguration:
## DATA STORAGE ##
datadir=/var/lib/mysql
## InnoDBConfiguration ##
innodb_file_per_table=1
# InnoDBMemory
innodb_buffer_pool_size= 2000M
# System Tablespace configuration
innodb_data_file_path= ibdata1:512M;ibdata2:512M:autoextend
# Redo log and buffer configuration
innodb-log-files-in-group=3
innodb_log_file_size=100M
innodb_log_buffer_size=30M
#InnoDBfile formate
innodb_file_format= Barracuda
# UNDO Tablespace Configuration
innodb_undo_directory= /var/lib/mysql/
innodb_undo_tablespaces= 3
innodb_undo_logs= 128
innodb_undo_log_truncate= ON
innodb_rollback_segments= 128
# Temp Tablespace Configuration
tmpdir= /var/lib/mysql/
innodb_temp_data_file_path= ibtmp1:20M:autoextend
# Keyring configuration
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring

44. Backups and Recovery
logical backup
this type of backup is created by saving information that represents the logical database structures using SQL statements like
create database, create table and insert. This type of backup is ideal when you want to upgrade from one version of MySQL to
another however it is a slower method of backing up.
physical backup
this type of backup is a backup of the actual database files or disk partitions, this type of backup can be very fast to backup and
restore.
full backup
a full backup is a standalone backup containing everything in the database, this could then be restored on another server. A full
backup can be either logical or physical.
incremental backup
this type of backup only contains the data that has changed from the last backup. The advantage of this type of backup is that it is
faster as there is not some much data to backup, however the disadvantage is that it takes longer to recover.
consistent backup this is a backup at an exact moment in time, generally you shutdown the database (or quiescent mode) then take the backup.
hot backup this type of backup is taken when the database is running, during the backup both reads and writes are not blocked
warm backup
this type of backup is taken when the database is running, however reads are not blocked but writes are prohibited from making
any modifications to the database.
cold backup similar to a consistent backup as the database is shutdown before the backup begins
point-in-time restore
is a restoration of a database to a specified date and time , some databases use a full backup and recovery logs to restore to that
point-in-time, others can only use the last full backup which means that data might have to be re-keyed into the system.

45. Backup tools for MySQL
Backup method Storage engineImpactBackup speedRecovery speedRecovery granularity
mysqldump ALL WARM MEDUIM SLOWEST MOST FLEXIBLE
mysqldump INNODB HOT MEDUIM SLOWEST MOST FLEXIBLE
select into outfile ALL WARM SLOW SLOW MOST FLEXIBLE
mk-parallel-backup ALL WARM MEDUIM MEDUIM FLEXIBLE
ibbackup INNODB HOT FAST FAST FLEXIBLE
ibbackup ALL WARM FAST FAST FLEXIBLE
backup command in mysqld ALL HOT FAST FAST FLEXIBLE
filesystem (copy files) ALL COLD FASTEST FASTEST NOT FLEXIBLE
snapshot (using LVM, ZFS, VMWare) ALL ALMOST HOT FAST FAST LEAST FLEXIBLE
mysqlhotcopy MyISAM MOSTLY COLD FAST FAST FLEXIBLE

46. mysqldump
## backup all databases
mysqldump--user=root --password --all-databases > backup_<date>_all.sql
## backup a specific database
mysqldump--user=root --password <database_name> > backup_<date>_<database_name>.sql
## backup multiple databases
mysqldump--user=root --password <database_name>,<database_name> > backup_<date>.sql
## backup a table from a database
mysqldump--user=root --password <database_name> <table_name> > backup_<date>_<database_name>_<table_name>.sql
## backup some specific data
mysqldump--user=root --password <database_name> <table_name> --where "last_name='VALLE' order by first_name>
backup_<date>.sql
## dumping from one database to another
mysqldump--databases <database_name> | mysql-h <destination_host> <database_name>
restore a mysqldump
## all databases
mysql--user=root --password < backup.sql
## specific database
mysql--user=<user> --password <database_name> < backup_<dataabse_name>.sql

47. select into
outfile/ load
data infile
## dump of the accounts table
select * into outfile'/tmp/accounts.txt' from accounts;
## load the dump
load data infile'/tmp/accounts.txt' into table accounts;
You can use the into outfileclause of the selectstatement to backup individual
tables, the command used to load the dump created is load data infile

48. The mysqlhotcopyis a perlscript written to provide a consistent backup of MyISAMand ARCHIVE
tables, it does some limitations one of which when run it uses the lock tables command to create
read locks on the tables being backed up, this allows for a consistent backup.
mysqlhotcopy
## backup a database
mysqlhotcopy<database_name> /backups
## backup multiple databases
mysqlhotcopy<database_name> accounts /backups
## backup a database to toanother server
mysqlhotcopy--method=scp<database_name> \[email protected]:/backup
## use pattern match to backup databases and tables
mysqlhotcopy<database_name>./^employees/ /backup

49. Recovering from Crashes
➢Most often you have to recover to a point-in-time after the last
backup, the normal procedure is as follows
➢restore the latest backup
➢recovery the data to a point-in-time using recovery log files
➢MySQL server uses a binary format for the log files to save
space, this means that you cannot view these files directly, a
utility called mysqlbinlogis supplied to convert these log files
into a text format that you can view. So the process for
performing a point-in-time restore for MySQL is
➢restore the database using the last backup
➢determine the first binary log and starting position needed
➢determine the last binary log needed
➢convert the binary log to text format with the mysqlbinlogutility
using options to specify the start and stop time
➢check the text file to make sure it's what you need
➢import the converted binary log(s)
convert the
log files
## convert to a specific binary log file
mysqlbinlogmysql-bin.010310 > mysql-
bin.010310.sql
## use a date to end at a specific time
mysqlbinlog--stop-datetime='201204-29 17:00:00'
mysql-bin.010312 > mysql-bin.010312.sql
## other options are
--stop-datetime
--start-datatime
--start-position
--stop-position
restore the
converted
file
mysql--user=root -password < mysql-bin.010310.sql

50. Point-in-Time (Incremental) Recovery Using the Binary Log
1.Point-in-Time Recovery Using Event Times
2.Point-in-Time Recovery Using Event Positions

51. Point-in-Time Recovery Using Event Times
To indicate the start and end times for recovery, specify the --start-datetime and --stop-datetime options for mysqlbinlog, in DATETIME format. As an
example, suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL statement was executed that deleted a large table. To restore the table and data,
you could restore the previous night's backup, and then execute the following command:
shell> mysqlbinlog--stop-datetime="2005-04-20 9:59:59" \
/var/log/mysql/bin.123456 | mysql-u root -p
This command recovers all of the data up until the date and time given by the --stop-datetime option. If you did not detect the erroneous SQL statement
that was entered until hours later, you will probably also want to recover the activity that occurred afterward. Based on this, you could run mysqlbinlog
again with a start date and time, like so:
shell> mysqlbinlog--start-datetime="2005-04-20 10:01:00" \
/var/log/mysql/bin.123456 | mysql-u root -p
In this command, the SQL statements logged from 10:01 a.m. on will be re-executed. The combination of restoring of the previous night's dump file and the
two mysqlbinlogcommands restores everything up until one second before 10:00 a.m. and everything from 10:01 a.m. on.
To use this method of point-in-time recovery, you should examine the log to be sure of the exact times to specify for the commands. To display the log file
contents without executing them, use this command:
shell> mysqlbinlog/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
Then open the /tmp/mysql_restore.sqlfile with a text editor to examine it.
Excluding specific changes by specifying times for mysqlbinlogdoes not work well if multiple statements executed at the same time as the one to be
excluded.

52. Point-in-Time Recovery Using Event Positions
Instead of specifying dates and times, the --start-position and --stop-position options for mysqlbinlogcan be used for
specifying log positions. They work the same as the start and stop date options, except that you specify log position
numbers rather than dates. Using positions may enable you to be more precise about which part of the log to recover,
especially if many transactions occurred around the same time as a damaging SQL statement. To determine the
position numbers, run mysqlbinlogfor a range of times near the time when the unwanted transaction was executed,
but redirect the results to a text file for examination. This can be done like so:
shell> mysqlbinlog--start-datetime="2005-04-20 9:55:00" --stop-datetime="2005-04-20 10:05:00"
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
This command creates a small text file in the /tmpdirectory that contains the SQL statements around the time that the
deleterious SQL statement was executed. Open this file with a text editor and look for the statement that you do not
want to repeat. Determine the positions in the binary log for stopping and resuming the recovery and make note of
them. Positions are labeled as log_posfollowed by a number. After restoring the previous backup file, use the position
numbers to process the binary log file. For example, you would use commands something like these:
shell> mysqlbinlog--stop-position=368312 /var/log/mysql/bin.123456 | mysql-u root -p
shell> mysqlbinlog--start-position=368315 /var/log/mysql/bin.123456 | mysql-u root -p
The first command recovers all the transactions up until the stop position given. The second command recovers all
transactions from the starting position given until the end of the binary log. Because the output of mysqlbinlog
includes SET TIMESTAMP statements before each SQL statement recorded, the recovered data and related MySQL logs
will reflect the original times at which the transactions were executed.

53. MySQL -Backup/Restore single table using mysqldump
Verification before restore
Check number of records
SELECT COUNT(*) FROM zabgate.host_inventory;
--5874 lines
Backup table steps using mysqldump
1) Create temporary backup directory (e.g/var/backups/zabgate/mysqldump) and give permission to mysql(ignore if path exist)
drwxr-xr-x 2 mysqlmysql47 Mar 27 04:39 mysqldump
2) Backup table using mysqldump
Command :
Single table : mysqldump-uitdba-p db_nametable_name> path/table_name.sql
Multiple table: mysqldump-u root -p dbnametable1 table2 table3 > table.sql
Execution :
mysqldump-uitdba-p zabgatehost_inventory> /var/backups/zabgate/mysqldump/host_inventory_190327.sql
Restore table steps
1) Create temp schema/ db
Mysql> create database temp_backup;
2) Move the sqlfile from mysqldumpdirectory to restore schema and make sure it's executable under mysql
mv /var/backups/zabgate/mysqldump/host_inventory_190327.sql /seamnt/prd101/mysql/temp_backup/
3) Restore table
Command :
mysql-u <user_name> -p db_name
mysql> source <full_path>/table_name.sql
Execution:
mysql> use temp_backup;
mysql> source host_inventory_190327.sql;
Verification after restore
Check number of records
SELECT COUNT(*) FROM temp_backup.host_inventory;
--5874 lines