Reprint Please specify Source:http://blog.csdn.net/guoyjoe/article/details/46697825
One, the Linux kernel and the release version
Uname-a
Cat/etc/issue
Second, the glibc version
/lib/libc.so.6---Dynamic link library with no man-letter data
Third, the MySQL version
The MySQL binary distribution version has the file name format: mysql-version-os.tar.gz
Example: Linux-generic (glibc 2.5) (x86,64bit), compressed TAR Archive (mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz)
1. Download
http://dev.mysql.com/downloads/mysql/
2, check RPM packaging in what directory
RPM-QPL mysql-server-5.6.23-1.el6.i686.rpm |more
RPM-QPL mysql-client-5.6.23-1.el6.x86_64.rpm |more
3. Change the RPM installation path
RPM--HELP
RPM--prefix--relocate
Rpmbuild Spec binary rpm
Yum Install
Iv. Installation of RPM
RPM-IVH XXX.RMP
RPM-PQL XXX.RMP
MySQL instance installation and startup
1. Installation
mysql_install_db--defaults-file=/root/data/mysql3306/my.cnf--basedir=/usr/--datadir=/root/data/mysql3306/data
2. Start
Mysqld_safe--DEFAULTS-FILE=/ROOT/DATA/MYSQL3306/MY.CNF &
3. Login
Mysql-h127.0.0.1-uroot-p3306-p
Five, installation demonstration:
1. Turn off MySQL
Ps-ef |grep mysqld
Kill 3397 3801
2. Installation
Cat init3306
SH init3306.sh
3. Start
Cat start3306.sh
SH start3306.sh
Note: mysql_install_db (generates mysql_install_db by installing the RPM package), like the following command view:
RPM-PQL mysql-server-5.5.42-1.linux2.6.i386.rpm |grep Install
Which mysql_install_db
4. Read the log
Tail-100f/root/data/mysql3306/log/alert.log
Occurs when a data dictionary does not exist, it is automatically created ....
5. View the process
Ps-ef |grep mysqld
Mysqld_safe is the parent process of mysqld
6. Login
SH my3306.sh--mysql-h127.0.0.1-uroot--p3306
Six, script:
1.---installation mysql:init3306.sh
rm-rf/root/data/mysql3306/data/*
rm-rf/root/log/mysql3306/iblog/*
rm-rf/root/log/mysql3306/binlog/*
Chmod-r 777/root/data/mysql3306/data/
Chmod-r 777/root/log/mysql3306/iblog/
Chmod-r 777/root/log/mysql3306/binlog/
chmod 755/root/data/mysql3306/my.cnf
mysql_install_db--defaults-file=/root/data/mysql3306/my.cnf--basedir=/usr/--datadir=/root/data/mysql3306/data
Chmod-r 777/root/data/mysql/3306/data/
Chmod-r 777/root/log/mysql3306/iblog/
Chmod-r 777/root/log/mysql3306/binlog/
2.--Start mysql:start336.sh
Mysqld_safe--DEFAULTS-FILE=/ROOT/DATA/MYSQL3306/MY.CNF &
3.--Login mysql:my3306.sh
Mysql-h127.0.0.1-uroot-p3306-p
Under the------study
Cd/root/data/mysql3306/data/mysql
Cd/root/data/mysql3306/data/performance_schema-Performance-related
Cd/root/data/mysql3306/data/test---Test library
cd/root/log/mysql3306/iblog/---innodb own data and logs
------Understanding MY.CNF (Multi-instance port to take points)
Vi/root/data/mysql3306/my.cnf
[Client]
port=3306
Socket=/root/data/mysql3306/run/mysql.sock
[MySQL]
port=3306
Promprt=\\[email protected]\\d \\r:\\m:\\s>
[Mysqld]
Default-storage-engine=innodb
Character-set-server=iatin1
Explicit_defaults_for_timestamp=true
#dir
Innodb_log_group_home_dir=/root/log/mysql3306/iblog
Innodb_data_home_dir=/root/log/mysql3306/iblog
Basedir=/usr
Datadir=/root/data/mysql3306/data
Tmpdir=/root/data/mysql3306/tmp
Slave_load_tmpdir=/root/data/mysql3306/tmp
Log-error=/root/data/mysql3306/log/alert.log
Slow_query_log_file=/root/data/mysql3306/log/slow.log
Relay_log_info_file=/root/log/mysql3306/binlog/relay-log.info
Master-info-file=/root/log/mysql3306/binlog/master.info
Socket=/root/data/mysql3306/run/mysql.sock
Log-bin=/root/log/mysql3306/binlog/binlog
Relay-log=/root/log/mysql3306/binlog/relaylog
Innodb_force_recovery=0
Vii. operation of MySQL
1. Log in to MySQL:
Local: Mysql-u$usrename-p$password
Remote: MYSQL-U$USERNAME-P$PASSWROD-H$IP
Multi-instance: Mysql-u$username-p$passwrod-p$port
2. User's operating
(1) Create a user
Method One:
Insert into Mysql.user (User,host,password) VALUES (' mytest ', ' localhost ', password (' 1234 '));
Flush privilege;
Method Two: Create user [email protected] '% ' identified by ' 1234 ';
(2) User authorization
Simple authorization.
Grant all privileges on * * to [email protected];
Grant Insert,update,delete,select on * * to [email protected];
Authorizing and creating users
Grant all privileges on * * to [e-mail protected] identified by ' 1234 ';---Create user and swipe cache,
(equivalent to: INSERT INTO Mysql.user, flush privilege)
Grant all privileges on * * to [email protected]; --Object permissions
Grant Super On * * to [email protected] '% '; --System permissions (Supert equivalent to DBA Authority in Oracle)
3, the actual exercise
show databases; --View all the databases
Use MySQL; --Cut to MySQL database
Use tables; --Tables in MySQL Library
Select User,host,password from Mysql.user; ----for all MySQL users, this is created by mysql_install_db
Grant all privilege on * * to [email protected] '% '; --all representative (select Update,delete,alter admin,super_acl), first * user, Second * object,% all hosts
Mysql-h127.0.0.1-utest_1----Log in to MySQL with a user created with Grant
Select User (); What users are currently---
Create Database Jianfeng; ---Create a database (databases in MySQL are similar to schemas in Oracle
CREATE table user (id int) ENGINE=INNODB---creating tables;
Grant Select on Jianfeng.user to [email protected] '% '; ---jianfeng.user table query to test_1 users
Insert into Mysql.user (User,host,password) VALUES (' test_2 ', '% ', password (' 1234 ')); --Create a test_2 user in this way, there is a problem permission is not
Flush privileges; ---to re-swipe the user rights of the Mysql.user table into memory
Show Master Status\g;
Change master to xxx;
Show Processlist; ---View the current user's connection, in the form of a thread (similar to v$session in Oracle)
4. DROP TABLE Processing
Rename table test_1 to test; (can be quickly cut back rename table test to test_1;)
Backup Mysqldump:mysqldump-h127.0.0.1-uroot mydb gyj_t1 >/tmp/gyj_t1.sql
drop table test;
5. Self-increment primary key (preferably the primary key, the system default is the global increment)
CREATE TABLE test (ID int primary key auto_increment,name varchar ()) Engine=innodb;
Show CREATE TABLE test\g;
CREATE INDEX TEST_NAME_IDX on test (name);
Show CREATE TABLE test\g;
INSERT into test (name) VALUES (' Test ');
Commit
SELECT * from Test;
6, ALTER TABLE processing-will move the original data, need to copy the data
ALTER TABLE test add coll int;
7. Implementation plan
SELECT * from test where id=1\g;
Explain select * from Test where id=1;
CREATE INDEX TEST_ID_COLL_IDX on test (id,coll);
Explain select * from Test where id=1;
CREATE INDEX test_col_name on test (coll,name);
Explain select * from Test where coll>10 and name= ' xx ';
Show CREATE TABLE test\g;
ALTER TABLE test DROP INDEX TEST_NAME_IDX;
Explain select * from Test where coll>10 and name> ' xx ';
8. Data export
(1) Export data with dump
Mysqldump-h127.0.0.1-uroot mydb gyj_t1 >/tmp/xx.sql
drop table test;
Source/tmp/xx.sql--Import data
(2) Export data with Select
SELECT * FROM test to outfile '/tmp/yy.sql ';
9. Data Migration
(1) Stop mode
Mysqldump/loadata
(2) Non-stop mode
Physical: library (can cascade 5.5-->5.6, backwards compatible)
The main library read only, the library can be transferred to the main library binlog digestion, and then the preparation of the base cut-based
Show variables like '%read% ';
Set global read_only=on;
INSERT into test (name) VALUES (' xx '); --cannot be plugged in, not with the root user
(3) Different Platform small table: Oracle--->mysql
Script: synfull.pl
(4) One large table move multiple on different platforms: incremental migration
A. Moving the entire amount of data into the past
B. Transfer the logs generated during the migration
C.apply Increment
D. Lock watch for replacement
(5) Incremental
A.oracle: Materialized view
B.mysql:trigger
Create Trigger Tri_test
Before Insert,delete,update
Insert Test_log value (TYPE,ID);
End
/
INSERT into test values (1, ' xxx ');
Test_log value (' Insert ', ' 1 ');
Lock table test;
Application switching
10, Binlog
Reset Master; --will clear out the current binlog.
Show Binlog events;
CREATE TABLE x1 (id int);
Show Binlog events;
INSERT INTO X1 values (1);
Commit
Show Binlog events;
Similar to: MYSQLBINLOG-VVV binlog.00001 >/tmp/binlog.log
Vi/tmp/binlog.log
Wal:write ahead log, logging priority
11. Archive
Flush logs;
Show master status;
Write ahead log. Recover Backup, duriably. Undo Acid MVCC
12. Parameters and statistical information
Show variables; ----parameters
Show variables like '%bin% ';
Show status; ----statistical information
Show global status like '%insert% ';
INSERT into test (name) values (' xxxxx ');
Show variables like '%default% ';
Set global Default_storage_engine=myisam; ---does not affect the operation of the current session, affecting the newly established connection
Set session Default_storage_engine=myisam; ---actions that affect the current session
---connection pool
Max_connect
Min_connect
Max_idle
Time_out
Disconnect--Release
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Koriyuki--Daily maintenance operations
