初次學習在Linux下配置Mysql，首先查詢版本，環境；部署後，啟動Mysql，檢驗資料庫是否執行正常

[@more@]--檢視LINUX版本
[root@sd ~]# cat /etc/issue
Red Hat Enterprise Linux AS release 4 (Nahant)
Kernel r on an m
[root@sd ~]# cat /proc/version
Linux version 2.6.9-5.ELsmp () (gcc version 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)) #1 SMP Wed Jan 5 19:30:39 EST 2005
--Kernel
[root@sd ~]# uname -r
2.6.9-5.ELsmp
--檢視CPU
[root@sd ~]# grep "model name" /proc/cpuinfo
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
--MEM
[root@sd ~]# free
total used free shared buffers cached
Mem: 4147280 1467312 2679968 0 105028 1141932
-/+ buffers/cache: 220352 3926928
Swap: 4192924 0 4192924
--資訊
[root@sd ~]# cat /proc/cpuinfo
-------------------------
---檢查linux軟體管理包
[root@sd ~]# rpm -qa system-config-packages
system-config-packages-1.2.23-1
--顯示mysql安裝包
[root@sd ~]# rpm -q mysql
mysql-4.1.7-4.RHEL4.1
[root@sd ~]# rpm -q apache
package apache is not installed
[root@sd ~]# rpm -q php
php-4.3.9-3.1
[root@sd ~]# rpm -q perl
perl-5.8.5-12
--列出安裝包的詳細列表
[root@sd ~]# rpm -qa |grep php
php-gd-4.3.9-3.1
php-pear-4.3.9-3.1
php-xmlrpc-4.3.9-3.1
php-ldap-4.3.9-3.1
php-pgsql-4.3.9-3.1
php-devel-4.3.9-3.1
php-mbstring-4.3.9-3.1
php-domxml-4.3.9-3.1
php-ncurses-4.3.9-3.1
php-odbc-4.3.9-3.1
php-snmp-4.3.9-3.1
php-4.3.9-3.1
php-imap-4.3.9-3.1
[root@sd ~]# rpm -qa |grep mysql
mysql-devel-4.1.7-4.RHEL4.1
mysql-4.1.7-4.RHEL4.1
mysqlclient10-devel-3.23.58-4.RHEL4.1
mysqlclient10-3.23.58-4.RHEL4.1
freeradius-mysql-1.0.1-2.RHEL4
libdbi-dbd-mysql-0.6.5-10.RHEL4.1
[root@sd ~]# rpm -qa |grep apache
[root@sd ~]# rpm -ql mysql-4.1.7-4.RHEL4.1
/etc/ld.so.conf.d/mysql-i386.conf
/etc/init.d/mysqld
/etc/my.cnf
/usr/bin/msql2mysql
/usr/bin/mysql
/usr/bin/mysql_config
/usr/bin/mysql_find_rows
/usr/bin/mysql_tableinfo
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog
/usr/bin/mysqlcheck
/usr/bin/mysqldump
/usr/bin/mysqldumpslow
/usr/bin/mysqlimport
/usr/bin/mysqlsho
/usr/share/info/mysql.info.gz
/usr/share/man/man1/mysql.1.gz
/usr/share/man/man1/mysqlaccess.1.gz
/usr/share/man/man1/mysqladmin.1.gz
/usr/share/man/man1/mysqldump.1.gz
/usr/share/man/man1/mysqlshow.1.gz
/usr/share/mysql
-------------------------------------------------------------
--Starting mysqld daemon with databases from /etc/init.d/mysqld
[root@sd mysql]# /etc/init.d/mysqld
Usage: /etc/init.d/mysqld {start|stop|status|condrestart|restart}
[root@sd mysql]# /etc/init.d/mysqld start
[root@sd mysql]# /etc/init.d/mysqld status
mysqld (pid 18373) is running...
[root@sd mysql]# 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:22273 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:32770 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 
[root@sd mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 4.1.7
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
察看mysql是否在自動啟動列表中
/sbin/chkconfig --list
[root@sd opt]# mysqladmin -u -p shutdown
mysqladmin: shutdown failed; error: 'Access denied; you need the SHUTDOWN privilege for this operation'
[root@sd opt]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
Stopping MySQL: [ OK ]
[root@sd opt]# ls /var/lib/mysql
ibdata1 ib_logfile0 ib_logfile1 mysql test
[root@sd opt]# mv /var/lib/mysql /opt/mydata/
[root@sd opt]# ls /var/lib/ -alt
[root@sd opt]# ls /opt/mydata/mysql
ibdata1 ib_logfile0 ib_logfile1 mysql test
[root@sd opt]# cd /opt/mydata/mysql
[root@sd mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql test
[root@sd mysql]# cd mysql
[root@sd mysql]# /etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]
[root@sd mysql]# /etc/init.d/mysqld status
mysqld (pid 18776) is running...
[root@sd mysql]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@sd mysql]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[root@sd mysql]# ps -ef |grep mysql
root 18845 17994 0 16:27 pts/1 00:00:00 grep mysql
[root@sd mysql]# vi /etc/init.d/mysqld
#!/bin/bash
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: - 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my.cnf
# pidfile: /var/run/mysqld/mysqld.pid
# Source function library.
. /etc/rc.d/init.d/functions
# Source networking configuration.
. /etc/sysconfig/network

prog="MySQL"
datadir="/var/lib/mysql"
#datadir="/opt/mydata/mysql"
Entering Ex mode. Type "visual" to go to Normal mode.
:wq
"/etc/rc.d/init.d/mysqld" 111L, 2497C written
[root@sd mysql]# vi /etc/my.cnf
[mysqld]
#datadir=/opt/mydata/mysql
datadir=/var/lib/mysql
#socket=/opt/mydata/mysql/mysql.sock
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Entering Ex mode. Type "visual" to go to Normal mode.
:q
[root@sd mysql]# cd /opt/mydata
[root@sd mydata]# ls
mysql
[root@sd mydata]# mv mysql /var/lib/
[root@sd mydata]# ls /var/lib/mysql
ibdata1 ib_logfile0 ib_logfile1 mysql test
[root@sd mydata]# /etc/init.d/mysqld status
mysqld is stopped
[root@sd mydata]# /etc/init.d/mysqld start
Starting MySQL: [ OK ]
[root@sd mydata]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3 to server version: 4.1.7
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> h
For the complete MySQL Manualonline, visit:
http://www.mysql.com/documentation
For info on technical support from MySQL developers, visit:

For info on MySQL books, utilities, consultants, etc., visit:

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (?) Synonym for `help'.
clear (c) Clear command.
connect (r) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set query delimiter.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
help (h) Display this help.
nopager (n) Disable pager, print to stdout.
notee (t) Don't write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
prompt (R) Change your mysql prompt.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute a SQL script file. Takes a file name as an argument.
status (s) Get status information from the server.
system (!) Execute a system shell command.
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (u) Use another database. Takes database name as argument.
For server side help, type 'help contents'
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
15 rows in set (0.00 sec)
mysql> describe db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(64) | | PRI | | |
| User | char(16) | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
+-----------------------+---------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
mysql> select * from db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
| % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
| % | test_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+
2 rows in set (0.00 sec)
mysql> create database sdtest;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| sdtest |
| test |
+----------+
3 rows in set (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
15 rows in set (0.00 sec)
mysql> use sdtest
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table name( id int(3) auto_increment not null primary key,csny date,xm char(9),xy char(9));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_sdtest |
+------------------+
| name |
+------------------+
1 row in set (0.00 sec)
mysql> select * from name;
Empty set (0.00 sec)
mysql> describe name ;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(3) | | PRI | NULL | auto_increment |
| csny | date | YES | | NULL | |
| xm | char(9) | YES | | NULL | |
| xy | char(9) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into name values('','1981-01-10','MM','SEXY');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from name;
+----+------------+------+------+
| id | csny | xm | xy |
+----+------------+------+------+
| 1 | 1981-01-10 | MM | SEXY |
+----+------------+------+------+
1 row in set (0.00 sec)
[root@sd mydata]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 4.1.7
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use sdtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into name values('','1981-03-30','GG','HANDSOME');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into name values('','1981-03-30','DY','SMITH');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from name;
+----+------------+------+----------+
| id | csny | xm | xy |
+----+------------+------+----------+
| 1 | 1981-01-10 | MM | SEXY |
| 2 | 1981-03-30 | GG | HANDSOME |
| 3 | 1981-03-30 | DY | SMITH |
+----+------------+------+----------+
3 rows in set (0.00 sec)
mysql> update name set xm='XX' where xm='DY';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from name;
+----+------------+------+----------+
| id | csny | xm | xy |
+----+------------+------+----------+
| 1 | 1981-01-10 | MM | SEXY |
| 2 | 1981-03-30 | GG | HANDSOME |
| 3 | 1981-03-30 | XX | SMITH |
+----+------------+------+----------+
3 rows in set (0.00 sec)
mysql> grant select ,insert,update,delete on *.* to hnzx_hq@"%" identified by "huangq";
Query OK, 0 rows affected (0.00 sec)
mysql> grant select ,update on sdtest.* to identified by "123";
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@sd mydata]# mysql -u hnzx_hq -p huangq
Enter password:
ERROR 1045 (28000): Access denied for user (using password: YES)
[root@sd mydata]# mysql -u hnzx_hq -p
Enter password:
ERROR 1045 (28000): Access denied for user (using password: YES)
[root@sd mydata]# mysql -u hnzx_hq -p -h
Enter password:
ERROR 1045 (28000): Access denied for user (using password: YES)
[root@sd mydata]# mysqldump -u root -p --opt sdtest >back_sdtest
Enter password:
[root@sd mydata]# ls /var/lib/mysql
ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock sdtest test
[root@sd mydata]# cd sdtest
-bash: cd: sdtest: No such file or directory
[root@sd mydata]# cd /var/lib/mysql/sdtest
[root@sd sdtest]# ls
db.opt name.frm name.MYD name.MYI
[root@sd sdtest]# mysqldump -u root -p --opt sdtest > back_sdtest
Enter password:
[root@sd sdtest]# ls /var/lib/mysql
ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock sdtest test
[root@sd sdtest]# cd /var/lib/mysql/sdtest
[root@sd sdtest]# ls
back_sdtest db.opt name.frm name.MYD name.MYI
[root@sd sdtest]# mysql -u root -p ccc < back_sdtest
Enter password:
ERROR 1049 (42000): Unknown database 'ccc'
[root@sd sdtest]# mysql -u root -p cc < back_sdtest
Enter password:
ERROR 1049 (42000): Unknown database 'cc'
[root@sd sdtest]#

LINUX下配置MYSQL

相關文章