Mysql installation and daily use

Mysql installation and daily use of bitsCN.com

1.1 MySQL database


N MySQL Performance Blog (www.mysqlperformanceblog.com)

N MySQL PUB (www.mysqlpub.com)

N MySQL Performance Tuning and architecture design

N exploring MySQL

1. install MySql


1. extract

Tar-xvfz mysql-5.0.21.tar.gz

2. environment configuration and installation

Cd mysql-5.0.21

./Configure -- prefix =/usr/local/mysql -- sysconfdir =/etc/

-- Localstatedir =/mnt/ext/mysql //

-- Enable-cycler -- with-charset = utf8/

-- With-extra-charsets = all -- with-berkeley-db -- with-innodb/

-- Without-readline -- enable-consumer er -- with-pthread/

-- Enable-largefile -- with-big-tables -- enable-thread-safe-client/

-- With-client-ldflags =-all-static/

-- With-max-indexes = 64 -- with-mysqld-ldflags =-all-static

-- Without-ndbcluster -- without-debug/

-- With-unix-socket-path =/tmp/mysql. sock

/* Description:

# Prefix =/usr/local/mysql target directory for mysql installation

# Sysconfdir =/etc my. ini configuration file path

# Localstatedir =/var/lib/path of the mysql database */

Make

Make install

3. mysql User permission configuration

Add mysql users and user groups

# Groupadd mysql

# Useradd-g mysql

Modify mysql directory permissions

# Chown-R root/usr/local/mysql

# Chgrp-R mysql/usr/local/mysql

# Chown-R mysql/mnt/ext/mysql

# Cp support-files/my-medium.cnf/etc/my. cnf

# Cp support-files/mysql. server/etc/init. d/mysql

4. set environment variables

Vi/etc/profile and vi/root/. bash_profile

# -- Basedir is used to set the directory where mysql is located, and -- datadir is used to set the directory where data is located

Export PATH =/usr/local/MySQL/bin: $ PATH =/usr/local/MySQL/libexec: $ PATH

-- Other options

Basedir = "/usr/local"

Bindir = "/usr/local/bin"

Mysqld = "/usr/local/libexec/mysqld"

Pkgdatadir = "/usr/local/share/mysql"

/* Description:/usr/local/mydql/bin is the main executable program directory of MySQL,

While libexec is the main MySQL background program MySQLd (2). bashrc configuration :*/

5. load MySql

-- # Initialize the database. skip this step if the database is upgraded.

Scripts/mysql_install_db -- user = mysql -- basedir =/usr/local/mysql/

-- Datadir =/mnt/ext/mysql

/Usr/local/mysql/bin/mysqld_safe -- user = mysql &

/* Note: If it cannot be started, it is a permission problem,

Modify the/var/lib/mysql permission chown-R mysql: mysql/var/lib/mysql

Or you can use root to start mysql:/usr/local/mysql/bin/mysqld_safe -- user = root &

If it cannot be started again, it is a problem with my. cnf configuration */

6. enable MySql

Modify the execution permission: chmod 775/etc/init. d/mysql

-- View mysql status:/etc/rc. d/init. d/mysql status

Start:/etc/rc. d/init. d/mysql start or service mysql start

Start the MYSQL server automatically at startup: chkconfig mysql on

#/Sbin/chkconfig -- del mysql

#/Sbin/chkconfig -- add mysql

Or

Add/usr/local/mysql/support-files/mysql. server start to vi/etc/rc. d/rc. local.

Reset the secret:/usr/local/bin/mysqladmin-u root password 'modified secret'

7. back up the database

-- Mysqldump: used to back up mysql: mysqldump-uroot-p database name> Backup Path

For example:/usr/local/bin/mysqldump-u root-p mysql>/root/mysql. SQL

8. import database

Mysql-u root-p database name <database file

-- Remote host connection, ip: xx. xx: YYYY, export data

Mysqldump-uUser-pPwd-h xx. xx-PYYYY Pwd> test. dump

Fix, check, and optimize the database file with the. ism suffix: isamchk

The database file used to fix the. myi suffix:

/Usr/local/bin/myisamchk/var/lib/mysql/*. MYI -- check all. MYI files in the msyql database.

9. data restoration reference

Use the-r parameter to fix the problem.

/Usr/local/bin/myisamchk-r/var/lib/mysql/*. MYI

Display the selected database and table: mysqlshow

/Usr/local/bin/mysqlshow-u root-p database name

10. common mysql commands

Shell> show variable; # View parameter configuration commands

Shell> show status; # view the status

Shell> show processlist; # View processes

Shell> show databases; # check the databases

Shell> use database_name; # specify the database

Shell> show tables; # View tables in the database

Shell> desc table_name # view the table structure

Shell> show create table table_name/G; # View detailed table creation information

Shell> show triggers/G

Shell> create database [if not exists] database_name; # create a database

Shell> drop database [if exists] database_name; # delete a database

How procedure status; // stored procedure

Show function status; // function

View the code for creating a stored procedure or function

Show create procedure proc_name;

Show create function func_name;

View

SELECT * from information_schema.VIEWS // view

SELECT * from information_schema.TABLES // table

Shell> mysql -- help; # View help

11. Other operations

Mysql> update user set password = PASSWORD ("123456") where User = 'root'; # -- update the password

Mysql> flush privileges; # -- refresh the permission

If you want to allow myuser to connect to the mysql server from a host whose ip address is 192.168.1.3, and use mypassword as the password

Grant all privileges on *. * TO 'hostname' @ 'IP' identified by 'psswd 'with grant option;

Grant select, INSERT, UPDATE, DELETE, CREATE, drop on mysql. * TO '*' @ '* 'identified BY 'passwd ';

The author's "Clement Ge's column"

BitsCN.com