blog
How to Manage MySQL – for Oracle DBAs
Open source databases are quickly becoming mainstream, so migration from proprietary engines into open source engines is a kind of an industry trend now. It also means that we DBA’s often end up having multiple database backends to manage.
In the past few blog posts, my colleague Paul Namuag and I covered several aspects of migration from Oracle to Percona, MariaDB, and MySQL. The obvious goal for the migration is to get your application up and running more efficiently in the new database environment, however it’s crucial to assure that staff is ready to support it.
This blog covers the basic operations of MySQL with reference to similar tasks that you would perform daily in your Oracle environment. It provides you with a deep dive on different topics to save you time as you can relate to Oracle knowledge that you’ve already built over the years.
We will also talk about external command line tools that are missing in the default MySQL installation but are needed to perform daily operations efficiently. The open source version doesn’t come with the equivalent of Oracle Cloud Control for instance, so do checkout ClusterControl if you are looking for something similar.
In this blog, we are assuming you have a better knowledge of Oracle than MySQL and hence would like to know the correlation between the two. The examples are based on Linux platform however you can find many similarities in managing MySQL on Windows.
How do I Connect to MySQL?
Let’s start our journey with a very (seemingly) basic task. Actually, this is a kind of task which can cause some confusion due to different login concepts in Oracle and MySQL.
The equivalent of sqlplus / as sysdba connection is “mysql” terminal command with a flag -uroot. In the MySQL world, the superuser is called root. MySQL database users (including root) are defined by the name and host from where it can connect.
The information about user and hosts from where it can connect is stored in mysql.user table. With the connection attempt, MySQL checks if the client host, username and password match the row in the metadata table.
This is a bit of a different approach than in Oracle where we have a user name and password only, but those who are familiar with Oracle Connection Manager might find some similarities.
You will not find predefined TNS entries like in Oracle. Usually, for an admin connection, we need user, password and -h host flag. The default port is 3306 (like 1521 in Oracle) but this may vary on different setups.
By default, many installations will have root access connection from any machine (root@’%’) blocked, so you have to log in to the server hosting MySQL, typically via ssh.
Type the following:
mysql -u rootWhen the root password is not set this is enough. If the password is required then you should add the flag -p.
mysql -u root -pYou are now logged in to the mysql client (the equivalent of sqlplus) and will see a prompt, typically ‘mysql>’.
Is MySQL up and Running?
You can use the mysql service startup script or mysqladmin command to find out if it is running. Then you can use the ps command to see if mysql processes are up and running. Another alternative can be mysqladmin, which is a utility that is used for performing administrative operations.
mysqladmin -u root -p statusOn Debian:
/etc/init.d/mysql statusIf you are using RedHat or Fedora then you can use the following script:
service mysqld statusOr
/etc/init.d/mysqld statusOr
systemctl status mysql.serviceOn MariaDB instances, you should look for the MariaDB service name.
systemctl status mariadbWhat’s in This Database?
Like in Oracle, you can querythe metadata objects to get information about database objects.
It’s common to use some shortcuts here, commands that help you to list objects or get DDL of the objects.
show databases;
use database_name;
show tables;
show table status;
show index from table_name;
show create table table_name;Similar to Oracle you can describe the table:
desc table_name;Where is my Data Stored?
There is no dedicated internal storage like ASM in MySQL. All data files are placed in the regular OS mount points. With a default installation, you can find your data in:
/var/lib/mysqlThe location is based on the variable datadir.
root@mysql-3:~# cat /etc/mysql/my.cnf | grep datadir
datadir=/var/lib/mysqlYou will see there a directory for each database.
Depending on the version and storage engine (yes there are a few here), the database’s directory may contain files of the format *.frm, which define the structure of each table within the database. For MyISAM tables, the data (*.MYD) and indexes (*.MYI) are stored within this directory also.
InnoDB tables are stored in InnoDB tablespaces. Each of which consists of one or more files, which are similar to Oracle tablespaces. In a default installation, all InnoDB data and indexes for all databases on a MySQL server are held in one tablespace, consisting of one file: /var/lib/mysql/ibdata1. In most setups, you don’t manage tablespaces like in Oracle. The best practice is to keep them with autoextend on and max size unlimited.
root@mysql-3:~# cat /etc/mysql/my.cnf | grep innodb-data-file-path
innodb-data-file-path = ibdata1:100M:autoextendInnoDB has log files, which are the equivalent of Oracle redo logs, allowing automatic crash recovery. By default there are two log files: /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1. Undo data is held within the tablespace file.
root@galera-3:/var/lib/mysql# ls -rtla | grep logfile
-rw-rw---- 1 mysql mysql 268435456 Dec 15 00:59 ib_logfile1
-rw-rw---- 1 mysql mysql 268435456 Mar 6 11:45 ib_logfile0Where is the Metadata Information?
There are no dba_*, user_*, all_* type of views but MySQL has internal metadata views.
Information_schema is defined in the SQL 2003 standard and is implemented by other major databases, e.g. SQL Server, PostgreSQL.
Since MySQL 5.0, the information_schema database has been available, containing data dictionary information. The information was actually stored in the external FRM files. Finally, after many years .frm files are gone in version 8.0. The metadata is still visible in the information_schema database but uses the InnoDB storage engine.
To see all actual views contained in the data dictionary within the mysql client, switch to information_schema database:
use information_schema;
show tables;You can find additional information in the MySQL database,which contains information about db, event (MySQL jobs), plugins, replication, database, users etc.
The number of views depends on the version and vendor.
Select * from v$session
Oracle’s select * from v$session is represented here with the command SHOW PROCESSLIST which shows the list of threads.
mysql> SHOW PROCESSLIST;
+---------+------------------+------------------+--------------------+---------+--------+--------------------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+---------+------------------+------------------+--------------------+---------+--------+--------------------+------------------+-----------+---------------+
| 1 | system user | | NULL | Sleep | 469264 | wsrep aborter idle | NULL | 0 | 0 |
| 2 | system user | | NULL | Sleep | 469264 | NULL | NULL | 0 | 0 |
| 3 | system user | | NULL | Sleep | 469257 | NULL | NULL | 0 | 0 |
| 4 | system user | | NULL | Sleep | 469257 | NULL | NULL | 0 | 0 |
| 6 | system user | | NULL | Sleep | 469257 | NULL | NULL | 0 | 0 |
| 16 | maxscale | 10.0.3.168:5914 | NULL | Sleep | 5 | | NULL | 4 | 4 |
| 59 | proxysql-monitor | 10.0.3.168:6650 | NULL | Sleep | 7 | | NULL | 0 | 0 |
| 81 | proxysql-monitor | 10.0.3.78:62896 | NULL | Sleep | 6 | | NULL | 0 | 0 |
| 1564 | proxysql-monitor | 10.0.3.78:25064 | NULL | Sleep | 3 | | NULL | 0 | 0 |
| 1822418 | cmon | 10.0.3.168:41202 | information_schema | Sleep | 0 | | NULL | 0 | 8 |
| 1822631 | cmon | 10.0.3.168:43254 | information_schema | Sleep | 4 | | NULL | 1 | 1 |
| 1822646 | cmon | 10.0.3.168:43408 | information_schema | Sleep | 0 | | NULL | 464 | 464 |
| 2773260 | backupuser | localhost | mysql | Query | 0 | init | SHOW PROCESSLIST | 0 | 0 |
+---------+------------------+------------------+--------------------+---------+--------+--------------------+------------------+-----------+---------------+
13 rows in set (0.00 sec)It is based on information stored in the information_schema.processlist view. The view requires to have the PROCESS privilege. It can also help you to check if you are running out of the maximum number of processes.
Where is an Alert log?
The error log can be found in my.cnf or via show variables command.
mysql> show variables like 'log_error';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| log_error | /var/lib/mysql/error.log |
+---------------+--------------------------+
1 row in set (0.00 sec)Where is the List of the Users and Their Permissions?
The information about users is stored in the mysql.user table, while the grants are stored in several places including the mysql.user, mysql.tables_priv,
MySQL user access is defined in:
mysql.columns_priv, mysql.tables_priv, mysql.db,mysql.userThe preferable way to list grants is to use pt-grants, the tool from Percona toolkit (a must-have for every MySQL DBA).
pt-show-grants --host localhost --user root --ask-passAlternatively, you can use the following query (created by Calvaldo)
SELECT
CONCAT("`",gcl.Db,"`") AS 'Database(s) Affected',
CONCAT("`",gcl.Table_name,"`") AS 'Table(s) Affected',
gcl.User AS 'User-Account(s) Affected',
IF(gcl.Host='%','ALL',gcl.Host) AS 'Remote-IP(s) Affected',
CONCAT("GRANT ",UPPER(gcl.Column_priv)," (",GROUP_CONCAT(gcl.Column_name),") ",
"ON `",gcl.Db,"`.`",gcl.Table_name,"` ",
"TO '",gcl.User,"'@'",gcl.Host,"';") AS 'GRANT Statement (Reconstructed)'
FROM mysql.columns_priv gcl
GROUP BY CONCAT(gcl.Db,gcl.Table_name,gcl.User,gcl.Host)
/* SELECT * FROM mysql.columns_priv */
UNION
/* [Database.Table]-Specific Grants */
SELECT
CONCAT("`",gtb.Db,"`") AS 'Database(s) Affected',
CONCAT("`",gtb.Table_name,"`") AS 'Table(s) Affected',
gtb.User AS 'User-Account(s) Affected',
IF(gtb.Host='%','ALL',gtb.Host) AS 'Remote-IP(s) Affected',
CONCAT(
"GRANT ",UPPER(gtb.Table_priv)," ",
"ON `",gtb.Db,"`.`",gtb.Table_name,"` ",
"TO '",gtb.User,"'@'",gtb.Host,"';"
) AS 'GRANT Statement (Reconstructed)'
FROM mysql.tables_priv gtb
WHERE gtb.Table_priv!=''
/* SELECT * FROM mysql.tables_priv */
UNION
/* Database-Specific Grants */
SELECT
CONCAT("`",gdb.Db,"`") AS 'Database(s) Affected',
"ALL" AS 'Table(s) Affected',
gdb.User AS 'User-Account(s) Affected',
IF(gdb.Host='%','ALL',gdb.Host) AS 'Remote-IP(s) Affected',
CONCAT(
'GRANT ',
CONCAT_WS(',',
IF(gdb.Select_priv='Y','SELECT',NULL),
IF(gdb.Insert_priv='Y','INSERT',NULL),
IF(gdb.Update_priv='Y','UPDATE',NULL),
IF(gdb.Delete_priv='Y','DELETE',NULL),
IF(gdb.Create_priv='Y','CREATE',NULL),
IF(gdb.Drop_priv='Y','DROP',NULL),
IF(gdb.Grant_priv='Y','GRANT',NULL),
IF(gdb.References_priv='Y','REFERENCES',NULL),
IF(gdb.Index_priv='Y','INDEX',NULL),
IF(gdb.Alter_priv='Y','ALTER',NULL),
IF(gdb.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
IF(gdb.Lock_tables_priv='Y','LOCK TABLES',NULL),
IF(gdb.Create_view_priv='Y','CREATE VIEW',NULL),
IF(gdb.Show_view_priv='Y','SHOW VIEW',NULL),
IF(gdb.Create_routine_priv='Y','CREATE ROUTINE',NULL),
IF(gdb.Alter_routine_priv='Y','ALTER ROUTINE',NULL),
IF(gdb.Execute_priv='Y','EXECUTE',NULL),
IF(gdb.Event_priv='Y','EVENT',NULL),
IF(gdb.Trigger_priv='Y','TRIGGER',NULL)
),
" ON `",gdb.Db,"`.* TO '",gdb.User,"'@'",gdb.Host,"';"
) AS 'GRANT Statement (Reconstructed)'
FROM mysql.db gdb
WHERE gdb.Db != ''
/* SELECT * FROM mysql.db */
UNION
/* User-Specific Grants */
SELECT
"ALL" AS 'Database(s) Affected',
"ALL" AS 'Table(s) Affected',
gus.User AS 'User-Account(s) Affected',
IF(gus.Host='%','ALL',gus.Host) AS 'Remote-IP(s) Affected',
CONCAT(
"GRANT ",
IF((gus.Select_priv='N')&(gus.Insert_priv='N')&(gus.Update_priv='N')&(gus.Delete_priv='N')&(gus.Create_priv='N')&(gus.Drop_priv='N')&(gus.Reload_priv='N')&(gus.Shutdown_priv='N')&(gus.Process_priv='N')&(gus.File_priv='N')&(gus.References_priv='N')&(gus.Index_priv='N')&(gus.Alter_priv='N')&(gus.Show_db_priv='N')&(gus.Super_priv='N')&(gus.Create_tmp_table_priv='N')&(gus.Lock_tables_priv='N')&(gus.Execute_priv='N')&(gus.Repl_slave_priv='N')&(gus.Repl_client_priv='N')&(gus.Create_view_priv='N')&(gus.Show_view_priv='N')&(gus.Create_routine_priv='N')&(gus.Alter_routine_priv='N')&(gus.Create_user_priv='N')&(gus.Event_priv='N')&(gus.Trigger_priv='N')&(gus.Create_tablespace_priv='N')&(gus.Grant_priv='N'),
"USAGE",
IF((gus.Select_priv='Y')&(gus.Insert_priv='Y')&(gus.Update_priv='Y')&(gus.Delete_priv='Y')&(gus.Create_priv='Y')&(gus.Drop_priv='Y')&(gus.Reload_priv='Y')&(gus.Shutdown_priv='Y')&(gus.Process_priv='Y')&(gus.File_priv='Y')&(gus.References_priv='Y')&(gus.Index_priv='Y')&(gus.Alter_priv='Y')&(gus.Show_db_priv='Y')&(gus.Super_priv='Y')&(gus.Create_tmp_table_priv='Y')&(gus.Lock_tables_priv='Y')&(gus.Execute_priv='Y')&(gus.Repl_slave_priv='Y')&(gus.Repl_client_priv='Y')&(gus.Create_view_priv='Y')&(gus.Show_view_priv='Y')&(gus.Create_routine_priv='Y')&(gus.Alter_routine_priv='Y')&(gus.Create_user_priv='Y')&(gus.Event_priv='Y')&(gus.Trigger_priv='Y')&(gus.Create_tablespace_priv='Y')&(gus.Grant_priv='Y'),
"ALL PRIVILEGES",
CONCAT_WS(',',
IF(gus.Select_priv='Y','SELECT',NULL),
IF(gus.Insert_priv='Y','INSERT',NULL),
IF(gus.Update_priv='Y','UPDATE',NULL),
IF(gus.Delete_priv='Y','DELETE',NULL),
IF(gus.Create_priv='Y','CREATE',NULL),
IF(gus.Drop_priv='Y','DROP',NULL),
IF(gus.Reload_priv='Y','RELOAD',NULL),
IF(gus.Shutdown_priv='Y','SHUTDOWN',NULL),
IF(gus.Process_priv='Y','PROCESS',NULL),
IF(gus.File_priv='Y','FILE',NULL),
IF(gus.References_priv='Y','REFERENCES',NULL),
IF(gus.Index_priv='Y','INDEX',NULL),
IF(gus.Alter_priv='Y','ALTER',NULL),
IF(gus.Show_db_priv='Y','SHOW DATABASES',NULL),
IF(gus.Super_priv='Y','SUPER',NULL),
IF(gus.Create_tmp_table_priv='Y','CREATE TEMPORARY TABLES',NULL),
IF(gus.Lock_tables_priv='Y','LOCK TABLES',NULL),
IF(gus.Execute_priv='Y','EXECUTE',NULL),
IF(gus.Repl_slave_priv='Y','REPLICATION SLAVE',NULL),
IF(gus.Repl_client_priv='Y','REPLICATION CLIENT',NULL),
IF(gus.Create_view_priv='Y','CREATE VIEW',NULL),
IF(gus.Show_view_priv='Y','SHOW VIEW',NULL),
IF(gus.Create_routine_priv='Y','CREATE ROUTINE',NULL),
IF(gus.Alter_routine_priv='Y','ALTER ROUTINE',NULL),
IF(gus.Create_user_priv='Y','CREATE USER',NULL),
IF(gus.Event_priv='Y','EVENT',NULL),
IF(gus.Trigger_priv='Y','TRIGGER',NULL),
IF(gus.Create_tablespace_priv='Y','CREATE TABLESPACE',NULL)
)
)
),
" ON *.* TO '",gus.User,"'@'",gus.Host,"' REQUIRE ",
CASE gus.ssl_type
WHEN 'ANY' THEN
"SSL "
WHEN 'X509' THEN
"X509 "
WHEN 'SPECIFIED' THEN
CONCAT_WS("AND ",
IF((LENGTH(gus.ssl_cipher)>0),CONCAT("CIPHER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL),
IF((LENGTH(gus.x509_issuer)>0),CONCAT("ISSUER '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL),
IF((LENGTH(gus.x509_subject)>0),CONCAT("SUBJECT '",CONVERT(gus.ssl_cipher USING utf8),"' "),NULL)
)
ELSE "NONE "
END,
"WITH ",
IF(gus.Grant_priv='Y',"GRANT OPTION ",""),
"MAX_QUERIES_PER_HOUR ",gus.max_questions," ",
"MAX_CONNECTIONS_PER_HOUR ",gus.max_connections," ",
"MAX_UPDATES_PER_HOUR ",gus.max_updates," ",
"MAX_USER_CONNECTIONS ",gus.max_user_connections,
";"
) AS 'GRANT Statement (Reconstructed)'
FROM mysql.user gus;How to create a mysql user
The ‘create user’ procedure is similar to Oracle. The simplest example could be:
CREATE user 'username'@'hostname' identified by 'password';
GRANT privilege_name on *.* TO 'username'@'hostname';The option to grant and create in one line with:
GRANT privilege_name ON *.* TO 'username'@'hostname' identified by 'password';has been removed in MySQL 8.0.
How do I start and stop MySQL?
You can stop and start MySQL with the service.
The actual command depends on the Linux distribution and the service name.
Below you can find an example with the service name mysqld.
Ubuntu
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
/etc/init.d/mysqld restartRedHat/Centos
service mysqld start
service mysqld stop
service mysqld restartsystemctl start mysqld.service
systemctl stop mysqld.service
systemctl restart mysqld.serviceWhere is the MySQL Server Configuration data?
The configuration is stored in the my.cnf file.
Until version 8.0, any dynamic setting change that should remain after a restart required a manual update of the my.cnf file. Similar to Oracle’s scope=both, you can change values using the persistent option.
mysql> SET PERSIST max_connections = 1000;
mysql> SET @@PERSIST.max_connections = 1000;For older versions use:
mysql> SET GLOBAL max_connections = 1000;
$ vi /etc/mysql/my.cnf
SET GLOBAL max_connections = 1000;How do I Backup MySQL?
There are two ways to execute a mysql backup.
For smaller databases or smaller selective backups, you can use the mysqldump command.
Database Backup with mysqldump (logical backup):
mysqldump -uuser -p --databases db_name --routines --events --single-transaction | gzip > db_name_backup.sql.gzxtrabackup, mariabackup (hot binary backup)
The preferable method is to use xtrabackup or mariabackup, external tools to run hot binary backups.
Oracle offers hot binary backup in the paid version called MySQL Enterprise Edition.
mariabackup --user=root --password=PASSWORD --backup --target-dir=/u01/backups/Stream Backup to Other Server
Start a listener on the external server on the preferable port (in this example 1984)
nc -l 1984 | pigz -cd - | pv | xbstream -x -C /u01/backupsRun backup and transfer to external host
innobackupex --user=root --password=PASSWORD --stream=xbstream /var/tmp | pigz | pv | nc external_host.com 1984Copy User Permission
It’s often needed to copy user permission and transfer them to the other servers.
The recommended way to do this is to use pt-show-grants.
pt-show-grants > /u01/backupsHow do I Restore MySQL?
Logical Backup Restore
MySQLdump creates the SQL file, which can be executed with the source command.
To keep the log file of the execution, use the tee command.
mysql> tee dump.log
mysql> source mysqldump.sqlBinary Backup Restore (xtrabackup/mariabackup)
To restore of MySQL from the binary backup you need to first restore the files and then apply the log files.
You can compare this process to restore and recover in Oracle.
xtrabackup --copy-back --target-dir=/var/lib/data
innobackupex --apply-log --use-memory=[values in MB or GB] /var/lib/dataHopefully, these tips give a good overview of how to perform basic administrative tasks.
