The only time you are allowed to change the setting of lower_case_table_names is in connection with initializing the MySQL data directory (mysqld –initialize). So you need to create a logical backup, then completely re-initialize MySQL, then restore the backup. Note: If you are using Group Replication or InnoDB Cluster, you must perform this procedure for […]
mysql
how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)
If you have set up binary logging and wish to restore a binary log, but want to omit one or more specific SQL statements from a data recovery, you can do so by using the mysqlbinlog utility and redirecting the results to a text file for editing. When you’re finished you can pass the edited […]
mysqldump – How to Restore a Specific Database From a Backup of All Databases
Sometimes you may need to restore a specific database, but the only available backup contains all databases. Restoring everything from a backup may not be an option. Instead, you will need the ability to restore only a specific database. Using the –one-database option allows you to restore a single database from a backup of multiple […]
Examples of mysqldump partial backups
Depending on whether you want a subset of databases or tables within a single database, there are various options to create the backup. Selecting the Databases and Tables to Include In the Backup In general the following is included in the backup: The CREATE TABLE statements for each table included in the backup INSERT statements […]
How to Restore a Specific Database or Table (MySQL)
Question: How to restore a specific table from a backup dump file containing all databases and tables? Sometimes you may need to restore specific tables, but the only available backup contains all databases. Restoring everything from a backup may not be an option. Instead, you will need the ability to restore only specific databases or […]
How to Create a MySQL Docker Container for Testing
Docker is a container runtime environment that allows programs to operate in a jailed environment without any required external dependencies. Containers are similar in some concepts to virtual machines, however, they do not use a hypervisor and run in a single kernel instance, often sharing the instance with other containers. One of the prime features […]
Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
MyISAM Storage Engine MyISAM was the default MySQL storage engine prior to the MySQL server version 5.5.5. The current default is the InnoDB storage engine. The mysql database contains tables in the MyISAM format. Each MyISAM table is represented by three files: Formatfile: Stores the definition of the table structure (mytable.frm) Data file: Stores the […]
Multi-Versioning in MySQL Database
MySQL database keeps the information about old versions of changed rows and supports transactional features such as concurrency and rollback. A rollback segment is an InnoDB storage area that contains the undo log. InnoDB can respond to queries for multiple versions of the same row when those queries are part of transactions that started at […]
How to use foreign keys to attain referential integrity in MySQL
Referential Integrity Referential integrity means that relationships between tables are consistent. MySQL enforces referential integrity by using foreign key constraints. When one table (the child table) has a foreign key to another table (the parent table), MySQL prevents you from adding a record to the child table if there is no corresponding record in the […]
Beginners Guide to Storage Engines in MySQL
Storage Engines and MySQL When you create a table, MySQL uses the InnoDB storage engine to create the storage for that table on the hard disk. You can choose an alternative storage engine to use for each table. Typically, you make this choice according to which storage engine offers features that best fit the needs […]