The installation of CentOS MySQL (RPM)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Today the installation of MySQL MySQL conflicts with mysql-4.1.20-2 when the error occurred.
check whether installed MySQL

rpm -qa mysql

Install the old version of MySQL

When you use rpm -e mysql-4.1.20-2.RHEL4.1.i386 uninstall error

Then check the information use

RPM -e mysql-4.1.20-2.RHEL4.1.i386 --nodeps

Then install the smooth

I installed server and client software.

Note that you want to delete these two files /etc/my.conf and/var/lib/mysql


Install the Mysql
1, download the MySQL installation file
MySQL installation requires below two files:
MySQL-server-5.1.7-0.i386.rpm

Download the address is: http://dev.mysql.com/downloads/mysql/5.1.html, Open this webpage, Drop webpage find “ Linux x86 generic RPM (statically linked against glibc 2.2.5 downloads&rdquo item);, Find the “ Server” and “ Client programs” items, Download the above two RPM files needed.
2, the installation of MySQL
    The RPM file is the development of Red Hat software package is installed, RPM allows Linux to install packages avoid many complicated procedures. The command in the installation of common parameter is – IVH, where I denotes the specified RMP package will be installed, V said details of the installation of the h, said &ldquo appears during installation; #” symbol to display the current installation process. This symbol will continue until after the installation is complete before stopping.
   1) the installation server
   Run the following command on two RMP files directory:
   [root@test1 local]# rpm -ivh MySQL-server-5.1.7-0.i386.rpm MySQL-client-5.1.7-0.i386.rpm  
Displays the following information.
warning: MySQL-server-5.1.7-0.i386.rpm
signature: NOKEY, key ID 5072e1f5
   Preparing...       ########################################### [100%]
   1:MySQL-server     ########################################### [100%]
    . . . . . . (omitted display)
   /usr/bin/mysqladmin -u root password 'new-password'
   /usr/bin/mysqladmin -u root -h test1 password 'new-password'
    . . . . . . (omitted display)
   Starting mysqld daemon with databases from /var/lib/mysql
   As a result of this information, the server installed. To test whether the successful running netstat Mysql port is open, such as open said service has been started, installed successfully. The default Mysql port is 3306.
   [root@test1 local]# netstat -nat
   Active Internet connections (servers and established)
   Proto Recv-Q Send-Q Local Address      Foreign Address     State   
   tcp  0  0 0.0.0.0:3306     0.0.0.0:*      LISTEN   
   Shown above can be seen in the MySQL service is started.
   2) to install the client
   Run the following command:
   [root@test1 local]# rpm -ivh MySQL-client-5.1.7-0.i386.rpm
   warning: MySQL-client-5.1.7-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
   Preparing...    ########################################### [100%]
   1:MySQL-client  ########################################### [100%]
   Display installed.
   To connect to the MySQL using the following command, test success.


Three, log on to MySQL

   Log on to the MySQL command is mysql, the use of MySQL syntax is as follows:
   mysql [-u username] [-h host] [-p[password]] [dbname]
    Username and password are respectively the username and password of MySQL account, the initial management of MySQL is root, no password, note: users of the system the root user is not the Linux. The MySQL default root user, since the initial no password, first enter only need to input mysql.
   [root@test1 local]# mysql
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 1 to server version: 4.0.16-standard
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql>
   The emergence of “ mysql> ” prompt, Congratulations, installed successfully!
   Increases logon format password:
   mysql -u root -p
   Enter password: (password)
   The -u heel is the username, -p requires a password, enter the password password is entered in the input.

   Note: This MySQL file in the /usr/bin directory, and later about the startup file /etc/init.d/mysql is not a file.

   Four, several important directory MySQL

   After the installation of MySQL don't like SQL Server is installed by default in a directory, the database files, configuration files and command files are in different directories, understanding these directory is very important, especially for beginners Linux, because the directory structure of Linux itself is more complex, if the installation directory do not know MySQL it is impossible talk about learning.

   Here's a look at the catalogue.

   1,The database directory
   /var/lib/mysql/

   2,Configuration file
/usr/share/mysql (mysql.server commands and configuration file)

   3,
/usr/bin related commands (mysqladmin mysqldump command)

   4,The startup script
/etc/rc.d/init.d/ (MySQL startup script file directory)
five, modify the login password

   The MySQL default no password, password installed increasing importance is self-evident.

   1,The command
usr/bin/mysqladmin
format: mysqladmin -u name -p the old password password password

   2,Examples of
1: add a password to the root 123456.
type the following command:
[root@test1 local]# /usr/bin/mysqladmin -u root password 123456
note: because there is no password at the start of root, so -p the old password a can be omitted.

   3,To test whether the modified successfully
   1) without a password
   [root@test1 local]# mysql
   ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
   Display error, explain the password has been modified.
   2) modified by login password
   [root@test1 local]# mysql -u root -p
   Enter password: (input modified code 123456)
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 4 to server version: 4.0.16-standard
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql>
   Success.
   This is to modify the password using the mysqladmin command, but also to change the password by modifying the library.


Six, start and stop

   1,Start the
after installing MySQL boot file MySQL in the /etc/init.d directory, run the following commands in the need to start.
   [root@test1 init.d]# /etc/init.d/mysql start

   2,Stop it
   /usr/bin/mysqladmin -u root -p shutdown

   3,Auto start
   1) to see whether MySQL in automatic startup list
   [root@test1 local]# /sbin/chkconfig --list
   2) add MySQL to start the service group of your system.
   [root@test1 local]# /sbin/chkconfig –- add mysql
   3) to remove MySQL from the launch services group.
   [root@test1 local]# /sbin/chkconfig –-del mysql
Seven, change the MySQL directory

   Data file storage directory MySQL the default is /var/lib/mysql. If the directory to /home/data requires the following steps:

   1,The establishment of data directory under the home directory
   cd /home
   mkdir data

   2,The MySQL service stopped:
   mysqladmin -u root -p shutdown

   3,The whole /var/lib/mysql directory to /home/data
MV /var/lib/mysql /home/data/
the MySQL data file to /home/data/mysql

   4,Find the my.cnf configuration file
if /etc/ directory without the my.cnf configuration file, please go to /usr/share/mysql/ to find *.cnf file, copy one to /etc/ and renamed the my.cnf). The following command:
   [root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

   5,Edit the MySQL configuration file /etc/my.cnf
   In order to ensure that MySQL can work normally, need to produce the location specified in mysql.sock file. As the value of modified socket=/var/lib/mysql/mysql.sock in a row on the right-hand side: /home/mysql/mysql.sock. The operation is as follows:
   VI my.cnf (edit the my.cnf file, using VI tool to find the following data modification)
   # The MySQL server
    [mysqld]
    port   = 3306
    #socket = /var/lib/mysql/mysql.sock (the original content, in order to better use “ #” note the trip)
    Socket = /home/data/mysql/mysql.sock (plus a trip)

   6,Modify the MySQL startup script /etc/rc.d/init.d/mysql
   Finally, need to modify the MySQL startup script /etc/rc.d/init.d/mysql, put the datadir=/var/lib/mysql in a row to the right of the equal sign, the path to your actual storage path: home/data/mysql.
   [root@test1 etc]# vi /etc/rc.d/init.d/mysql
   #datadir=/var/lib/mysql (note the trip)
   Datadir=/home/data/mysql (plus a trip)

   7,Restart the MySQL service
/etc/rc.d/init.d/mysql start
or use the reboot command to restart the Linux
if normal mobile will succeed, otherwise control in front of 7 step check again.


Eight, common operations MySQL

   Note: the MySQL of each command to end with a semicolon;.

   1,Display database
   mysql> show databases;
   +----------+
   | Database |
   +----------+
   | mysql  |
   | test   |
   +----------+
   2 rows in set (0.04 sec)
   Mysql has just installed a two databases: MySQL and test. The MySQL library is very important, it has MySQL system information, we change the password and the new users, is actually the operation with related tables in the database.

   2,Display tables in the database.
mysql> use MySQL; (open database, for each database operation to open the database, similar to FoxPro)
   Database changed

   mysql> show tables;
   +-----------------+
   | Tables_in_mysql |
   +-----------------+
   | columns_priv  |
   | db       |
   | func      |
   | host      |
   | tables_priv   |
   | user      |
   +-----------------+
   6 rows in set (0.01 sec)

   3,Display structure data table:
describe table name;

   4,Display records in the table:
select * from table name;
for example: display record in the user table in the MySQL database. All MySQL users can operate the user in this table.
   Select * from user;

   5,Database:
   Create database library;
   For example: to create a name for a AAA Library
   mysql> create databases aaa;
6, built in table:
   Use library;
   The create table table name (field setting list);
   For example: in the newly created AAA database table name, ID table (serial number, automatic growth), XM (name), XB (sex), CSNY (date of birth) four fields
   use aaa;
   mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date);
   You can look at the table structure was established using the describe command.
   mysql> describe name;

   +-------+---------+------+-----+---------+----------------+
   | Field | Type  | Null | Key | Default | Extra     |
   +-------+---------+------+-----+---------+----------------+
   | id  | int(3) |   | PRI | NULL  | auto_increment |
   | xm  | char(8) | YES |   | NULL  |        |
   | xb  | char(2) | YES |   | NULL  |        |
   | csny | date  | YES |   | NULL  |        |
   +-------+---------+------+-----+---------+----------------+

   7,Add record
   For example: add several records.
   Mysql> insert into name values ('', 'three', 'male','1971-10-01');
   Mysql> insert into name values ('', 'white cloud', 'female','1972-05-20');
   The select command can be used to verify the results.
   mysql> select * from name;
   +----+------+------+------------+
   | id | xm  | xb  | csny    |
   +----+------+------+------------+
   | 1 | Zhang San | male | 1971-10-01 |
   | 2 | Baiyun | female  | 1972-05-20 |
   +----+------+------+------------+


8,
Modify record for example: Zhang San's date of birth to 1971-01-10
mysql> update name set csny='1971-01-10'where xm=' Zhang San';

   9,
delete record for example: delete the Zhang San record.
mysql> delete from name where xm='Zhang San';

   10,Delete and delete database table
drop database library;
drop table table name,

   Nine, increase of MySQL users

   Format: Grant select on database. To test2 @ host identified by "password"
1, add a user user_1 password is 123, so that he can log in any host, and query, all database insert, modify, delete. First, for root users connected to the MySQL, and then type the following command:

   mysql> grant select,insert,update,delete on *.* to user_1@"%" Identified by "123";
Example 1 the increase of users is very dangerous, if you know the user_1 password, then he can log in to your MySQL database on any computer on the web and data for you to do as one pleases, solution see example 2.

  In 2 cases, add a user user_2 password is 123, so that the user can login on localhost, and AAA for database query, insert, modify, delete operations (localhost refers to the local host, the MySQL database where the host), namely the use of know the user_2 password so that the user, he also cannot access the database directly from the Internet, only through the MYSQL host to operate AAA database.

   mysql>grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123";

   Use the add user if not logged in MySQL, with the following command at logon:

   mysql -u user_1 -p -h 192.168.113.50 (-h heel is to log on the host IP address)

   Ten, backup and recovery

   1,Backup

   For example: the create AAA database backup to file back_aaa

   [root@test1 root]# cd /home/data/mysql (into the library catalog, the library has been from val/lib/mysql to /home/data/mysql, see above seventh parts)
   [root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

   2,Recovery

   [root@test mysql]# mysql -u root -p ccc <back_aaa 


Do you want myuser to use mypassword to connect to the MySQL server from any computer.

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
If you want to allow the user to myuser from the IP to the MySQL server for the 192.168.1.3 host is connected, and use mypassword as the password
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Page at November 09, 2013 - 7:38 AM