New answers tagged

2

OPTIMIZE is probably better because what it does is CREATE TABLE ... Copy all the existing rows over RENAME ... (Some time during or after step 2, the indexes are rebuilt.) Dump and reload: Read the entire table, write to disk DROP TABLE or TRUNCATE TABLE and CREATE TABLE ... Read the dump, inserting into the table This is slower because of the ...


-3

I'm so surprised no one just posted 'mysql -v' as an option. If it gets stuck, the output will stop.


1

16777216 is a reasonable value; 1024 is not. The error message is just a warning, saying that 1024 is below the minimum allowed value. Try the mysqldump with 16777216.


2

Un-panic. Your queue had lots of rows inserted and quickly deleted, correct? And now it has very few rows in it? Specifically, about 17K? More importantly, there was, at one point, maybe a million rows? So the tablespace expanded to handle it, but has not shrunken. The dump dumped only the data for the current 17K rows. The reload loaded only those ...


2

Please note this Pictorial Representation of ibdata1 (from Vadim Tkachenko) You have many moving parts inside ibdata1 Data Dictionary Double Write Buffer Insert Buffer Rollback Segments Undo Space Almost 3 years ago, I wrote a post addressing why ibdata1 can grow too fast : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set? ...


0

How long does the backup take? How much does it "lock up" the Slave? What time of day is the backup done? What time of day are analytics performed? The answers to those questions will tell you "Will the backup interfere too much with the analytics queries?" The answer to that is the answer to your original question.


0

When running this command mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000004 | tail -n 5 | head -n 1 it gives an error. After investigation we found that last week there was power failure, because of which replication isn't happening. So the workaround is to take a new backup of master and restore the replication.


0

It is a good idea to use a slave for backups, especially if backups cause problems with operations on the master. Reading from the slave shouldn't present any issues - a lot of people do it as a way to lighten the load on the master. Check that the slave is set to read-only and be aware that long-running SELECTs may cause replication lag and possibly ...


0

Not a proper slave because you are not using mysql replication but anyway it's very common to have an slave or more than one to reduce the load in the master server. Make really sure that its only used for read queries.


0

I tried to solve this in many ways but, didn't got any workaround. then I tried to redo the whole thing again with the below steps. I took backup of mysql user from Master Host mysqldump -u root -p mysql user > user_table_dump.sql and Restored in on slave mysql -u root -p mysql < user_table_dump.sql At the time of restored the master dump on ...


0

The error itself is happening because there is a create view (or procedure) statement and the slave doesn't have the user who created it. In mysqldump you will have this format: CREATE ALGORITHM=UNDEFINED DEFINER=`lipl_ga_app`@`%` SQL SECURITY DEFINER... That's not specific to replication. It happens even if you would want to restore it to an empty db. ...


0

(n.b. I've never used AWS or RDS so there may be some differences). But as a general rule a MySQL DUMP file is just a series of CREATE TABLE and INSERT INTO statements, that when combined create your database. Ultimately it depends on what options were chosen as to what does and doesn't get included. But I think that by default it includes the ...


-2

You could enable the general log to track what's modified or you could also check the binlogs. With flush logs you can force to create a new binlog. And with mysqlbinlog you can check all modifications. To enable general log: mysql> SET GLOBAL general_log_file = '/mysql/log/all_queries.log'; mysql> SET GLOBAL general_log = 'ON'; mysql> SET GLOBAL ...


0

I had this error once, after running mysqldump on Windows PowerShell like so: mysqldump -u root p my_db --no-data --no-create-db --no-create-info --routines --triggers --skip-opt --set-gtid-purged=OFF > db_objects.sql What I did was change it to this (pipe instead to Set-Content): mysqldump -u root p my_db --no-data --no-create-db --no-create-info ...


1

This issue has been dealt with here. The approach described therein assumes that you have already separated your schema dump by filename. However, if I've understood you correctly, you have a schema dump from which you wish to extract a single file? I have written a small tool which extracts individual table data separately from a schema dump file. It's ...



Top 50 recent answers are included