Mysql installation and daily use 1

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-Cycler -- 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"