mysql安装及常见设置

安装MYSQL:

install_mysql(完整)
   1  tar -zxf mysql-5.0.27.tar.gz 
   2  cd mysql-5.0.27
   3  ./configure --prefix=/usr/local/mysql --sysconfdir=/etc --localstatedir=/var/lib/mysql

--with-extra-charsets=gbk
   4  make && make install
   5  groupadd mysql
      useradd mysql -c "start mysqlds account" -d /dev/null -g mysql -s /sbin/nologin       

         --创建mysql帐户,可以用mysql帐户来启动MYSQL服务
   6  /usr/local/mysql/bin/mysql_install_db   --初始化数据库
   7. chmod -R 777  /var/lib/mysql            --设置mysql状态报告目录的权限 
      chmod -R 777  /usr/local/mysql/
   8. cp /usr/local/mysql/share/mysql/my-medium.cnf /etc/my.cnf  --复制配置文件
      cp support-files/mysql.server /etc/rc.d/init.d/mysqld      --复制启动文件
      chmod 700 /etc/rc.d/init.d/mysqld                          --设置权限
   9. /usr/local/mysql/bin/mysqld_safe --user=root &          --用ROOT用户启动mysql 服务器
      /usr/local/mysql/bin/mysqld_safe --user=mysql &         --用mysql帐户启动mysql服务器
   10./usr/local/mysql/bin/mysqladmin -u root password 1234  --初始化root密码
       (/usr/local/mysql/bin/mysqladmin -u root -p password 456  --修改root已设置好的密码
   11. /usr/local/mysql/bin/mysql -u root -p                 --用新密码连接数据库

---------------------------------
启动MYSQL:
  
  /usr/local/mysql/bin/mysqld_safe --user=root &
  /etc/rc.d/init.d/mysqld start

----------------------------------
修改ROOT帐户密码:

  /usr/local/mysql/bin/mysqladmin -u root password 123  --初始化root密码
  /usr/local/mysql/bin/mysqladmin -u root -p password 456  --修改root已设置好的密码

----------------------------------
  mysql>create database king;  --创建数据库(king)
  mysql>show databases;    --查看数据库
  mysql>use king;
  mysql>show tables;
  mysql>drop database king;

--------------------------------
创建表:
  mysql>create table student(
      ->sno varchar(7) not null,
      ->sname varchar(20) not null,
      ->ssex char(1) default 't',
      ->sbirthday date,
      ->sdepa char(20),
      ->primary key (sno)
      ->);
复制表:

  mysql>create table sname like xs;   --将表sname复制为另一个表xs

  mysql>drop table xs;  --删除xs表

  mysql>alter table student add saddress varchar(25);  --在student表中增加saddress字段
  mysql>alter table student change address sremark text;  --将student表中的address字段名改为

                                                           sremark,字段类型改为text
 

  mysql>alter table student drop sremark;  --删除student表中的sremark字段

  grant all on *.* to  'chemguider'@'%' identified by 'chemguider-4';  --添加用户chemguider
  grant file on *.* to  'chemguider'@'%' identified by 'chemguider-4'; 
  grant all privileges on *.* to  'chemguider'@'%' identified by 'chemguider-4' with grant

option;

  mysql>use mysql;
        update user set password=password('123456') where user='backup';  --修改用户密码

解决mysql连接缓慢
修改my.ini
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
skip-name-resolve

 

/usr/local/mysql/mysqldump -h localhost -u root -p123456 test >/opt/test.sql    //备份本机的test数据库

mysql -u root -p123456  test </opt/test.sql        //恢复本机的test数据库

 

use test;

select * from data into outfile 'a.txt';     //导出data表中的数据另存为a.txt文件

load data infile 'a.txt' into table data;     //将a.txt文件中的数据导入到data表中

本文转自king_819 51CTO博客，原文链接：http://blog.51cto.com/kerry/102141，如需转载请自行联系原作者