You need to restart mysql from the Linux command line like this:
service mysql restart --skip-grant-tables --skip-networking
If you are using Windows, add the two options in the mysqld section of my.ini:
[mysqld]
skip-grant-tables
skip-networking
and then run
net stop mysql (wait 10 seconds)
net start mysql
This will allow immedidate login without authentication and without any remoye connection sneaking in on you.
INSERT INTO mysql.user SET
Host='localhost',
User='mynewuser',
Password=PASSWORD('whateverpassword'),
Select_priv='Y',
Insert_priv='Y',
Update_priv='Y',
Delete_priv='Y',
Create_priv='Y',
Drop_priv='Y',
Reload_priv='Y',
Shutdown_priv='Y',
Process_priv='Y',
File_priv='Y',
Grant_priv='Y',
References_priv='Y',
Index_priv='Y',
Alter_priv='Y',
Show_db_priv='Y',
Super_priv='Y',
Create_tmp_table_priv='Y',
Lock_tables_priv='Y',
Execute_priv='Y',
Repl_slave_priv='Y',
Repl_client_priv='Y';
then
service mysql restart
At least you will have a localhost user named mynewuser
with enough privileges to login.
You should then run this query:
select user,host,password from mysql.user;
and see if any new users were added by a hacker. If you see any remote users that should not be there, you can remove them with:
DELETE FROM mysql.user WHERE ...;
FLUSH PRIVILEGES;
If you are using Windows, please remember to remove skip-grants-tables
and skip-networking
from my.ini, then stop and start mysql service.
CAVEAT
Make a backup of /var/lib/mysql/mysql
before you do anything.
UPDATE 2011-09-26 10:41 EDT
If the INSERT INTO mysql.user query does not work, it simply means the query is not compatible with the version of mysql I took this example from.
In order to know the correct INSERT query to user, please run this query:
desc mysql.user;
You will see all columns with the type ENUM('Y','N') for each privilege. Adjust the INSERT query to match all columns and then run that INSERT.
To get the exact columns to set to Y for all privileges, user this query:
SELECT CONCAT('SET ... ',GROUP_CONCAT(CONCAT(column_name,'=''Y'''),';'))
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name='user'
AND column_type='enum(''N'',''Y'')'
ORDER BY ORDINAL_POSITION\G
This will present to columns to set regardless of the version of MySQL you are using.
+1 to @RolandoMySQLDBA of course for another valiant answer. But more to the point of your question:
...will [there] be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a [write] transaction.
YES.
You can't get a consistent backup even for a single MyISAM table unless you do some type of locking to prevent writes. Rolando gave a pretty thorough answer with options available to you for locking.
One other option that people use for backing up MyISAM data: LVM snapshots. See http://www.lenzg.net/mylvmbackup/ for a great tool to assist with this.
The final recommendation is to stop using MyISAM, and use InnoDB instead. Then you can do fast, non-locking physical backups with Percona XtraBackup.
Re your comment:
Because reading through a large file isn't instantaneous or atomic. While your backup is progressing through the table, other concurrent updates could change both rows that your backup has already read, and rows that your backup hasn't reached yet.
Take a textbook example for transaction behavior: I do a bank transfer by debiting my bank account and crediting your bank account. My bank account is stored on a row that is physically early in the file, and your bank account is stored on a row later in the file.
While this is going on, the backup is reading through the file, and it has read up to a mid-point at the time our transaction happens. When we restore, we get my original account balance, without the debit applied, because the backup had already passed that point when the debit occurred (and it isn't able to go backwards). But the restore includes your updated account balance, because the backup got to that point in the file after we increased your balance.
Ergo, free money! ;-)
MyISAM solves this by requiring the table to be locked against updates while it's doing a read.
InnoDB solves this by keeping multiple versions of rows, for as long as a reading transaction needs to see them for the sake of a consistent view of the database. So anyone can update the data without waiting, even though the backup is in progress.
Percona XtraBackup solves this in a slightly different way: it can go backwards, in a way. While it's reading the data file, it keeps checking the transaction log continually, to see if there are any late changes it needs to include. These changes may apply to parts of the datafile that Percona XtraBackup has already read. But as long as it gets the data file plus any changes that were logged since the backup started, it can reconstruct the full database.
But that only works for storage engines like InnoDB, that create a reliable transaction log. Percona XtraBackup can also back up MyISAM, but only by using locking, like any other backup tool.
Best Answer
Do not
OPTIMIZE
tables before backups. That consumes a lot of time, thereby making there by more 1-minute dumps. AndOPTIMIZE
rarely does anything useful.You have "max file size" = 0 KB -- does that mean unlimited, or small? I see that the files tend to be a few hundred KB.