MySQL Cluster

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

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

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

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

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

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

Question: How to list tables inside tablespaces in a MuSQL cluster? The following query meets the aim of the question, lists the database, table name and the corresponding tablespace: SELECT DISTINCT t.table_schema, t.table_name, p.tablespace_name FROM partitions p JOIN TABLES t ON (t.table_schema=p.table_schema and t.table_name = p.table_name) WHERE t.engine='ndbcluster' and tablespace_name IS NOT NULL … [Read more...] about How to List Tables Per Tablespace In MySQL Cluster

Note: In MySQL Server 8.0.X you must use the exact same patch release of MEB as the Server, eg MySQL Server 8.0.13 requires the use of MEB 8.0.13. The Basics For *nix OSes, the document uses a shell expansion for simple naming of folders with "todays" date+time, eg shell:~ # d="$(date '+%d%b_%H%M')" shell:~ # echo $d 13Nov_1338 It is, of course, possible to adjust this as any existing naming conventions dictate. Simple high level backup / restore commands: creates an image backup ( … [Read more...] about Backup and Restore Of Group Replication Node ( MySQL 8.0 )

The Problem A replication slave cannot continue replicating from its Master. One such example is that after being stopped for a long ( or longer than anticipated ) timeframe, the BINARY LOGS required on the Master have been purged. [ERROR] [MY-013114] [Repl] Slave I/O for channel 'group_replication_recovery': Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or … [Read more...] about MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB