Mysql database notes (Q &)

1. Problem:

When using the mysql database, when a large number of tables are generated and a large number of records are inserted, the/usr/local/mysql/data directory is generated.

Into some log files, such as: localhost-bin.001, localhost-bin.002 ......, These files are very large and occupy several GB.

My space soon disappeared. How can I control it and not write these logs? I read the log file content, which contains some

SQL statement, such as insert..., update ......

A: Yes. Non-error logs are used for Incremental backup and synchronization between multiple mysql databases.

Edit the my. cnf file, comment out log-bin = mysql-bin, and restart mysql.

Or FLUSH Logs

2. Question: How to put the MYSQL data DIRECTORY somewhere else?

Stop mysql and copy the file to the desired location;

Open the my. cnf file and edit the datadir file;

Start mysql

3. backup and recovery

The general BACKUP command is mysqldump. Here we will take the tm database as an example to briefly introduce

Backup:

# Mysqldump-u root-p tm> tm_20060101. SQL

Enter the password as prompted. In this case, all the table structures and data of the tm database are backed up to tm_20060101. SQL, because the backup work is always required.

If the data volume occupies a large space, you can use gzip to compress the data. The command is as follows:

# Mysqldump-u root-p tm | gzip> tm_20060101. SQL .gz

You can also back up the data to a remote machine, which is determined by-h, as shown in figure

# Mysqldump-u root-p tm> tm_20060101. SQL-h xxx. xxx

You can back up data directly to a remote computer with the IP address xxx.

When the system crashes, the system is rebuilt, or the database is restored, data can be restored as follows:

# Mysql-u root-p tm use mysql

> Update user set password = password ("new_pass") where user = "root ";

> Flush privileges;

> Exit

Use Ctrl + Alt + Del to find the mysqld-nt process and kill it. After restarting the mysql-nt service, you can log on with the new password.

In linux:

If MySQL is running, killall-TERM mysqld is first killed.

Start MySQL: bin/safe_mysqld -- skip-grant-tables &

You can access MySQL without a password.

Then

> Use mysql

> Update user set password = password ("new_pass") where user = "root ";

> Flush privileges;

Kill MySQL again and start MySQL in a normal way.

6. Why is the following prompt displayed:

Can't connect to local MySQL server through socket '/tmp/mysql. sock' (2)

Note: It may also be mysql. sock in another path.

A: The mysql service is not started. mysql. sock is A file generated after the mysql service is started,

Generally, killall mysql is killed first.

Then, start mysql based on your installation. For example, during installation

./Configure -- prefix =/usr/local/mysql

Start with the following command:

/Usr/local/mysql/bin/mysqld_safe -- user = mysql &

Then run/usr/local/mysql/bin/mysql-u root-p to log on to the mysql database.

7. Why are the following errors?

#1251-Client does not support authentication protocol requested by server; consider

Upgrading MySQL client

If the database you are using is more than 4.1, use the command line to connect to the MySQL database and execute the following command:

Update mysql. user SET password = OLD_PASSWORD ("your_password") Where Host = "your_host" AND

User = "your_username ";

.