Question: How to backup the users and their privileges as CREATE USER and/or GRANT statements? The method you can use depends on the version of MySQL you are using. MySQL 5.7 and Later In MySQL 5.7 and later, the recommended way to backup the users is to use mysqlpump. This has built-in support for backing up the users and their privileges through the --users option: $ mysqlpump --exclude-databases=% --users Note: You may need to add connection parameters such as --socket, --host, … [Read more...] about MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
mysql
How to Backup a MySQL Cluster
Basics There are two common options for backing up a MySQL cluster database: mysqldump or a native cluster backup. The utility mysqldump is more general since it is a storage engine independent. It has an advantage in that you can usually restore to a different table type with only minor effort. Also with it, backups can be limited to only certain databases or even tables. Since the dump file is written as SQL INSERT statements, it may take some time to restore, though. Note: It is strongly … [Read more...] about How to Backup a MySQL Cluster
How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
This post applies both to Server and Cluster environments, as cluster replication uses the MySQL Server / API node to facilitate the replication. There are minor config differences between the two databases, however, the core replication configuration is similar. The main requirement is that an existing M/S (Master/Slave) replication channel exists and is functioning nominally in the environment, which will then be extended to M/M (Master/Master). Configuring the existing replication SLAVE … [Read more...] about How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
How to Set Space limits for MySQL for database/schema/table
MySQL itself has not such quotas, but it is possible to partially manage quota limitation at engine level. For MyISAM it is possible to limit table size using option myisam_data_pointer_size This variable limits default data pointer size: less this variable is, less rows would be allowed to be saved in the table. "Table is full" error will be reported if this option is considered. For InnoDB it is possible to specify hard limit of shared tablespace: syntax is similar to … [Read more...] about How to Set Space limits for MySQL for database/schema/table
Counting Rows Of A Table In MySQL Server
Let's understand what are the available options to get an accurate number or an estimation of rows in a MySQL table. The option to get an accurate count of rows in a table is to perform: SELECT COUNT(*) FROM [table]; In MyISAM this operation, when no WHERE clause is provided, is very quick, as the storage engine knows at any time how many rows are in the table. When dealing with InnoDB storage engine, instead, there is no counter available. This is due to the transactional nature of this … [Read more...] about Counting Rows Of A Table In MySQL Server
MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
InnoDB Table Maintenance If a table check indicates problems, restore the table to a consistent state by dumping it with mysqldump, dropping it, and re-creating it from the dump file. In the event of a crash of the MySQL server or the host on which it runs, some InnoDB tables might be in an inconsistent state. InnoDB performs auto-recovery as part of its startup sequence. Use CHECK TABLE or a client program to find inconsistencies, incompatibilities, and other problems. Restore a table by … [Read more...] about MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE
Understanding mysqlcheck and myisamchk utilities
mysqlcheck Client Program In some cases, mysqlcheck is more convenient than issuing SQL statements directly. For example, if you provide it with a database name as its argument, mysqlcheck determines what tables the database contains and issues statements to process them all. You do not need to provide explicit table names as arguments. Also, because mysqlcheck is a command-line program, you can easily use it in operating system jobs that perform scheduled maintenance. mysqlcheck is a … [Read more...] about Understanding mysqlcheck and myisamchk utilities
MySQL – How to undo (rollback) a set of SQL statements
What is Conducting a Transaction? If you have a series of SQL statements that you want to run which involve adding, deleting, or changing data contained in InnoDB or BDB tables, but want to be sure that all SQL statements or transactions are completed successfully before committing them, there is a set of MySQL statements that you can use to do this. This is known as conducting a transaction (i.e., the set of SQL statements to execute) and being able to perform a rollback. Currently, you can … [Read more...] about MySQL – How to undo (rollback) a set of SQL statements
Beginners Guide to Implementing Table Maintenance in MySQL
Table maintenance operations are useful for identifying and correcting database problems such as the following: – Tables that become damaged as a result of a server crash. – Slow query processing on tables. Many tools are available for performing table maintenance: MySQL Workbench MySQL Enterprise Monitor SQL (DML) maintenance statements Utilities: mysqlcheck and myisamchk Server auto-recovery SQL for Table Maintenance Operations There are multiple SQL statements for performing … [Read more...] about Beginners Guide to Implementing Table Maintenance in MySQL
MySQL – How to Set Maximum Rates for Connections and Queries
There are several options to implement firewall like features for a MySQL instance. Some of these are described below. When an account is mentioned it refers to the combination of a username connected from a given host, i.e. username@hostname. MySQL Enterprise Firewall MySQL Enterprise Firewall will allow you to create a whitelist per account specifying the normalized queries that the account are allowed to execute. It is available in the Enterprise Edition of MySQL Server in versions … [Read more...] about MySQL – How to Set Maximum Rates for Connections and Queries