mysql home_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

use test;

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

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