18
votes
How can I monitor the progress of an import of a large .sql file?
Every 2 seconds you will see the processes running.
watch 'echo "show processlist;" | mysql -uuser -ppassword';
If you want it less frequent then add -n x where x is the number of seconds. 5 seconds ...
4
votes
Replication from MySQL 5.0 to MariaDB 10.2.8 - Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193
I found the solution/workaround. I need to set the variable slave_sql_verify_checksum=0 on the slave (MariaDB 10.2.8). I was trying this by dynamically setting the global variable on the slave but it ...
- 81
4
votes
How can I monitor the progress of an import of a large .sql file?
Rob's answer is great for most situations, but Pipe Viewer doesn't work well in use cases where a tty isn't available, like when monitoring a mysql docker container's initialization output or when you ...
- 141
4
votes
How can I monitor the progress of an import of a large .sql file?
I had a 500 MB SQL file to import. It took me around 2 hours. The mysqld CPU usage was next to 100 % at the start of the import process. But after a few minutes the CPU usage was down to 15 %.
I ...
- 41
2
votes
How can I monitor the progress of an import of a large .sql file?
For someone who is looking for the pipe viewer example using mysqldump you would just doing something like this:
mysqldump -hxxx -uxxx -p dbname | pv -W > dump.sql
The -W flag just tells pv to ...
- 121
2
votes
How can I monitor the progress of an import of a large .sql file?
If your DB is otherwise quiet (i.e. there are not other users active) and you want to just see read/write activity why not just do something like:
mysqladmin -h<host>-uroot -p<yourpass> ...
- 21
2
votes
Replication from MySQL 5.0 to MariaDB 10.2.8 - Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193
5.0 to 10.2 is about 4 major jumps. So, I am not surprised that you encountered a problem. If you like, file a bug with MariaDB about it. Normally a newer Slave will successfully replicate from an ...
- 79.4k
2
votes
mysqldump --no-data and --single-transaction
If you only want a dump of the schema there is really no point in using the --single-transaction option. This option is more useful towards obtaining consistent data from transactional tables. In fact,...
- 83
2
votes
Accepted
Access denied for user 'root'@'localhost' using Password NO
It's due to incorrect permissions on /etc/mysql/debian.cnf file.
Do a long-listing of that file first to check current permissions:
sudo ls -l /etc/mysql/debian.cnf
If group or other fields has ...
- 36
2
votes
Opening MySQL connection is sometimes very slow
Resolving host names may cause such an issue as I met before.
You might be able to fix it by adding these 2 options to your my.cnf:
skip-host-cache
skip-name-resolve
Then restart the MySQL server.
...
- 121
1
vote
Damaged HDD - How to recover a MySQL database?
Is it possible to save data from a damaged HDD that I still have access to?
I would say it was unlikely and should absolutely not be your first choice.
Hoping to get anything back from such a device ...
- 9,486
1
vote
Accepted
How to fetch the started on date when a price is changed (price is not incremental)
Maybe the following query will give you a starting point for writing your own ... Principle: select 4 columns from your customer_items table (derived table dt). Then, use 2 user-defined variables ...
- 2,507
1
vote
MySQL - Unix timestamp from columns with hour, day, month
Instead of transforming the columns in the WHERE clause, do so with the constants given by the application. Those calculations only have to be done once.
We can write the condition in a short, ...
- 237
1
vote
How can I monitor the progress of an import of a large .sql file?
A more detailed explanation for newbies like me of @Robbs Answer https://dba.stackexchange.com/a/28646/224874
It may not be of very much help, but using pv and pipes in Linux, you can create a ...
Only top scored, non community-wiki answers of a minimum length are eligible