FAQ and answers to MySQL on CU

Mysql| Solutions | questions

Q: How to Install lamp (linux+apache+mysql+php)

A:http://www.freelamp.com/1003235699/index_html

Q: How to install MySQL using the RPM method

A: First download the appropriate RPM package, such as downloading the file mysql-5.0.15-0.i386.rpm

Use the method to install:

#rpm-ivhu mysql-5.0.15-0.i386.rpm

Usually, after installing this RPM package, only has the MYSQLD service function, other related client program and the development package also needs to install separately

#rpm-ivhu mysql-devel-5.0.15-0.i386.rpm

#rpm-ivhu mysql-client-5.0.15-0.i386.rpm

Q: How to install a MySQL binary package that has been compiled

A: First download the appropriate binary package, such as downloading the file mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz

#groupadd MySQL

#useradd-G MySQL MySQL

#cd/usr/local

#tar ZXF mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz

#ln-S mysql-standard-4.1.13-pc-linux-gnu-i686 MySQL

#cd MySQL

#scripts/mysql_install_db--user=mysql

#chgrp-R MySQL *

#bin/mysqld_safe--user=mysql &

What personalized configuration can be achieved by creating/etc/my.cnf or/usr/local/mysql/data/my.cnf and adding relevant parameters.

Q: How to compile your own MySQL

A: Take Redhat Linux 9.0 for example:

Download File mysql-4.1.13.tar.gz

#tar ZXF mysql-4.1.13.tar.gz

#cd mysql-4.1.13

#./configure--prefix=/usr/local/mysql--enable-assembler \

--with-mysqld-ldflags=-all-static--localstatedir=/usr/local/mysql/data \

--with-unix-socket-path=/tmp/mysql.sock--enable-assembler \

--with-charset=complex--with-low-memory--with-mit-threads

#make

#make Install

#groupadd MySQL

#useradd-G MySQL MySQL

#chgrp-R mysql/usr/local/mysql/

#/usr/local/mysql/bin/mysqld_safe--user=mysql &

What personalized configuration can be achieved by creating/etc/my.cnf or/usr/local/mysql/data/my.cnf and adding relevant parameters.

Q: How to Login to MySQL

A: Log in using the client tools provided by MySQL

#PATH_TO_MYSQL/bin/mysql-uuser-ppassword Dateabase

Q: Forget the MySQL root password, how to modify

A: If MySQL is running, first kill: Killall-term mysqld.

Start Mysql:path_to_mysql/bin/mysqld--skip-grant-tables &

You can enter MySQL without the need for a password.

Then it's

Mysql>use MySQL

Mysql>update User Set Password=password ("New_pass") where user= "root";

Mysql>flush privileges;

Re-kill MySQL, start MySQL in the normal way

Must NOTE: Many beginners do not use Password=password ("..."), but directly password= "..." so get rid of the password is not good

Q: Why mysqld up, but unable to log in, the hint "/var/lib/mysql/mysql.sock" does not exist

A: Most of this is because your MySQL is installed using RPM, it will automatically look for/var/lib/mysql/mysql.sock this file,

Log on to MySQL via UNIX sockets.

Common solutions are as follows:

1,

Create/Modify file/etc/my.cnf, add/modify at least one line

[MySQL]

[Client]

Socket =/tmp/mysql.sock

#在这里写上你的mysql. Sock the correct position, usually either under/tmp/or under/var/lib/mysql/

2,

Specify an IP address and use TCP to connect to MySQL without using the local sock method

#mysql-h127.0.0.1-uuser-ppassword

3,

Add a connection to the mysql.sock, such as the actual mysql.sock under/tmp/, then

#ln-S/tmp/mysql.sock/var/lib/mysql/mysql.sock can

Q: How to modify the MySQL user password

A: There are roughly 2 ways:

1,

Mysql>mysql-uroot-pxxx MySQL

Mysql>update user Set Password=password (' New_password ') where user= ' user ';;

Mysql>flush privileges;

2,

Format: Mysqladmin-u username-P Old password password new password

#mysqladmin-uroot-password AB12

Note: Since Root does not have a password at the beginning, the-p old password can be omitted.

Q: How to add a MySQL user

A: Format: Grant SELECT on database. * To User name @ login host identified by "password"

Example 1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect the root user to MySQL, and then type the following command:

Mysql>grant select,insert,update,delete on *.* to test1@ "%" identified by "ABC";

But for example 1 the increased user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and can do whatever it wants with your data, as shown in Example 2.

Example 2, add a user test2 password for ABC, so that he can only log on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database of the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.

Mysql>grant select,insert,update,delete on mydb.* to Test2@localhost identified by "ABC";

If you do not want to test2 the password, you can make another command to eliminate the password.

Mysql>grant select,insert,update,delete on mydb.* to Test2@localhost identified by "";

Alternatively, you can do this by inserting a new record directly into the user table.

Q: How to see what database MySQL has

A:mysql>show databases;

Q: How do I see what table is under the database

A:mysql>show tables;

Q: How to export data

A: There are several methods, as follows:

1. Use mysqldump

#mysqldump-uuser-ppassword-b Database--tables table1--tables table2 > Dump_data_20051206.sql

Detailed parameters

2. Backup to Syntax

Mysql>backup TABLE Tbl_name[,tbl_name ...] To '/path/to/backup/directory ';

Please see the MySQL manual for details

3, Mysqlhotcopy

#mysqlhotcopy db_name [/path/to/new_directory]

Or

#mysqlhotcopy db_name_1 ... db_name_n/path/to/new_directory

Or

#mysqlhotcopy db_name./regex/

Please see the MySQL manual for details

4, select INTO outfile

Please see the MySQL manual for details

5, Client command line

#mysql-uuser-ppassword-e "SQL statements" Database > Result.txt

In all of the above methods, the most commonly used mysqldump

Q: How to execute an SQL statement on the command line

A: #mysql-uuser-ppassword-e "SQL statements" database

Q: How to import backed up files

A: Several common methods are as follows:

1, by the mysqldump out of the document

#mysql-uuser-ppassword [Database] < Dump.sql

2, file type ditto, use source syntax

mysql>source/path_to_file/dump.sql;

3, in a certain format stored text files or CSV and other documents

#mysqlimport [Options] Database file1 [file2 ...]

Please see the MySQL manual for details

4, file Type Ditto, you can also use the load data syntax to import

Please see the MySQL manual for details