I run a nightly mysqldump of all my databases and one of them has over 500 tables, mysqldumper bring me a error while processing this huge database: [Read more…]
Access denied for user ‘debian-sys-maint’
While trying to start or stop MySQL I get:
1 | Access denied for user 'debian-sys-maint'@'localhost' (using password: YES) |
Dabian has a own way to login to the MySQL database the credantials are listed in a file:
1 2 3 4 | cat /etc/mysql/debian.cnf [...] user = debian-sys-maint password = bodCNLUx0GGYm6F |
Logon:
1 | mysql -u root -p |
Execute:
1 | GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'bodCNLUx0GGYm6F' WITH GRANT OPTION; |
By the way set a new root passwort:
1 | mysqladmin -u root password 'new-password' |
MySQL Error Codes
Sometimes you need a good reference, and this is one of those times. MySQL’s perror command returns many error codes, and I’ve provided a listing here. To find errors individually, just run:
1 2 | # perror 28 OS error code 28: No space left on device |
Show MySQL restore progress
Sometimes if I work with really huge MySQL databases, I miss the progress bar of the restore progress.
I found a little solution to see the progress 
Description:
pv allows a user to see the progress of data through a pipeline, by giving information such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA.
1 2 | pv database.sql | mysql -D databasename -pyourpassword 948.4MB 0:00:03 [22.7MB/s] [=====> ] 21% ETA 0:00:11 |
MySQL: Show processlist every second
1 | mysqladmin -u root -p -i 1 processlist |
-u username
-p you will be promted for a password
-i 1 interval one second
Output will see like this one:
1 2 3 4 5 6 7 8 9 | +--------+------+-------------------+-----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-------------------+-----+---------+------+-------+------------------+ | 424111 | db4 | 192.168.2.1:58431 | db4 | Sleep | 15 | | | | 424195 | db4 | 192.168.2.1:50531 | db4 | Sleep | 1 | | | | 424483 | db3 | 192.168.2.1:41503 | db3 | Sleep | 464 | | | | 424490 | db3 | 192.168.2.1:41513 | db3 | Sleep | 443 | | | | 424551 | root | localhost | | Query | 0 | | show processlist | +--------+------+-------------------+-----+---------+------+-------+------------------+ |