MariaDB Crash Course - Ben Forta [85]
* * *
* * *
Tip: Simplifying Multiple Grants
Multiple GRANT statements may be strung together by listing the privileges comma delimited, as seen in this example:
GRANT SELECT, INSERT ON crashcourse.* TO bforta;
* * *
Changing Passwords
To change user passwords use the SET PASSWORD statement. New passwords must be encrypted as seen here:
Input
SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
Analysis
SET PASSWORD updates a user password. The new password must be encrypted by being passed to the Password() function.
SET PASSWORD can also be used to set your own password:
Input
SET PASSWORD = Password('n3w p@$$w0rd');
Analysis
When no user name is specified, SET PASSWORD updates the password for the currently logged in user.
Summary
In this chapter, you learned about access control and how to secure your MariaDB server by assigning specific rights to users. As you can imagine, there is a lot more to this advanced topic, and MariaDB administrators should dedicate the time to fully understand managing DBMS security.
29. Database Maintenance
In this chapter, you learn how to perform common database maintenance tasks.
Backing Up Data
Like all data, MariaDB data must be backed up regularly. As MariaDB databases are disk-based files, normal backup systems and routines can back up MariaDB data. However, as those files are always open and in use, normal file copy backup may not always work as is not recommended.
Here are possible solutions to this problem:
• Use the command line mysqldump utility to dump all database contents to an external file. This utility should ideally be run before regular backups occur so the dumped file will be backed up properly.
• The command line mysqlhotcopy utility can be used to copy all data from a database (this one is not supported by all database engines).
• You can also use MariaDB to dump all data to an external file using BACKUP TABLE or SELECT INTO OUTFILE. Both statements take the name of a system file to be created, and that file must not already exist or an error will be generated. Data can be restored using RESTORE TABLE.
* * *
Tip: Flush Unwritten Data First
To ensure that all data is written to disk (including any index data) you might need to use a FLUSH TABLES statement before performing your backup.
* * *
Performing Database Maintenance
MariaDB features a series of statements that can (and should) be used to ensure that databases are correct and functioning properly.
Here are some statements you should be aware of:
• ANALYZE TABLE is used to check that table keys are correct. ANALYZE TABLE returns status information, as seen here:
Input
ANALYZE TABLE orders;
Output
+--------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| crashcourse.orders | analyze | status | OK |
+--------------------+---------+----------+----------+
• CHECK TABLE is used to check tables for a variety of problems. Indexes are also checked on an ARIA or MyISAM table. CHECK TABLE supports a series of modes for use with ARIA or MyISAM tables. CHANGED checks tables that have changed since the last check, EXTENDED performs the most thorough check, FAST only checks tables that were not closed properly, MEDIUM checks all deleted links and performs key verification, and QUICK perform a quick scan only. As seen here, CHECK TABLE found and repaired a problem:
Input
CHECK TABLE orders, orderitems;
Output
+------------------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------+-------+----------+----------------------------+
| crashcourse.orders | check | status | OK |
| crashcourse.orderitems | check | warning | Table is marked as crashed |
| crashcourse.orderitems | check | status | OK |
+------------------------+-------+----------+----------------------------+
• If ARIA or MyISAM table access produces incorrect and inconsistent