Database

I am trying to backup mysql using the command

mysqldump -u root -p  database_name > backup.sql

but it is throwing an error:

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Also, i wanted to make a backup my database with all tables saperately sotored as a file. How can i do it?

 

Solution:

use --no-tablespaces

Thursday, 14 February 2019 21:40

Unknown collation: ‘utf8mb4_unicode_520_ci'

Written by

The following error is found when trying to migrate the MySQL database from one server to another: 

Unknown collation: ‘utf8mb4_unicode_520_ci’

-----------------------------

User can solve this by finding

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

in the .sql file, and swapping it with

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Tuesday, 19 June 2018 21:42

MySQL Service wont start (FIX!!!)

Written by

I gave up on 5.0 and installed 4.1 and I am up and running but I stumbled across this post deep in the forum. I bet this works but I dont want to mess with my installation anymore so I am not 100% sure: 

"Then I found the answer: one has to delete three files, all located in the mysql/data directory: 

*ib_logfile0 

*ib_logfile1 

*ibdata1 

Deleting these files means that the Windows config wizard recreates them and the server starts without a hitch. 

I also then restarted Windows after I reconfigured MySql just to make sure. 

Then run "MySQL Server Instance Config Wizard" again and enjoy!!!!!!!!!!!"

Dump of the MySQL database hosted on the Plesk server fails:

mysqldump: Got error: 1146: Table '<TABLENAME>' doesn't exist when using LOCK TABLES

Backups have the following warning related to the database:

WARNING: mysql 'exampleDB'

Unable to execute SQL: Table 'exampleDB.<TABLENAME>' doesn't exist in engine. SQL query: SHOW FULL COLUMNS IN `<TABLENAME>`

Migration fails with the following error:

Failed to copy content of database 'exampleDB'

Tuesday, 19 June 2018 21:33

Rebuilding or Repairing Tables or Indexes

Written by

This section describes how to rebuild or repair tables or indexes, which may be necessitated by:

Changes to how MySQL handles data types or character sets. For example, an error in a collation might have been corrected, necessitating a table rebuild to update the indexes for character columns that use the collation.

Required table repairs or upgrades reported by CHECK TABLE, mysqlcheck, or mysql_upgrade.

Methods for rebuilding a table include:

  1. Dump and Reload Method
  2. ALTER TABLE Method
  3. REPAIR TABLE Method

Dump and Reload Method

If you are rebuilding tables because a different version of MySQL will not handle them after a binary (in-place) upgrade or downgrade, you must use the dump-and-reload method. Dump the tables before upgrading or downgrading using your original version of MySQL. Then reload the tables after upgrading or downgrading.

I did change the datadir of a MySQL installation and following some steps it worked fine. Every base I had was moved correctly but one.

I can connect and USE the database, even SHOW TABLES returns me all the tables correctly and the files of each table exists on the mysql data directory. But when I try to SELECT something there, it says the table doesn't exists. But the table does exists, it even shows at SHOW TABLES statement!

My guess is that the SHOW TABLES lists the files existence somehow that the files are corrupt or something like that but it doesn't check it. So I can list them but not access them.

But that's just a guess, I've never seen this before. Can't restart the database now for testing, every other application which uses it is running fine.

Does anyone knows what is it?