Database Reference
In-Depth Information
Once a problem query (or set of queries) has been identified, the next step is to
implement a fix of some sort. This could be as simple as rewriting the query to be
more efficient. Or it could be sped up by adding an index to the table.
We can also gather and examine user and table statistics to identify patterns of usage
that we can potentially optimize. Or we can examine our table definitions to see if
there are any tweaks that can be made there to make things faster or more efficient.
The list of ways to squeeze more performance out of MariaDB goes on and on.
If the query and our database are as optimized as we can make them, there are still
things that we can do. Hardware, for example, can be a limitation. A busy database
that needs to respond quickly needs to be on fast hardware. Fast disks, lots of
memory, and a fast processor are all important.
More information on optimizing and tuning MariaDB can be found at
https://mariadb.com/kb/en/optimization-and-tuning/ .
Backing up MariaDB
MariaDB ships with a couple of utilities that can be used to back up our databases.
Data in MariaDB is written to special files on disk, so it may be tempting to think
that we can just backup the MariaDB data directory and be done. The problem with
this is that the data files are always open and in use while MariaDB is running and
problems can arise if we try to back up the files directly.
Basic backups with mysqldump
By default, the mysqldump client backup utility generates SQL backups. These
backups contain all the necessary SQL commands to recreate tables and restore the
data in those tables.
There are many options, but the basic syntax is as follows:
mysqldump [-u username] [-p] database_name [table_name]
If table_name is not given, mysqldump will backup all the tables in the named
database. For example, the following command will backup the entire test database:
mysqldump -u root -p test > test.sql
The output of mysqldump goes to standard out. When running the command from
a terminal, it will be echoed directly to the screen. So in the preceding example
command, we use the > redirect character to direct the output into a file named
test.sql .
 
Search WWH ::




Custom Search