MySQL O & M-routine maintenance operations, mysql-Routine Maintenance
Reprinted please indicate the source:Http://blog.csdn.net/guoyjoe/article/details/46697825
I. Linux kernel and release version
Uname-
Cat/etc/issue
Ii. glibc version
/Lib/libc. so.6 --- Dynamic Link Library without man function data
Iii. MySQL version
The file name format for MySQL binary distribution is: mysql-VERSION-OS.tar.gz
For 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 the directory in which rpm is packaged
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. rpm Installation
Rpm-ivh xxx. rmp
Rpm-pql xxx. rmp
Install and start a MySQL instance
1. Install
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. log on
Mysql-h127.0.0.1-uroot-P3306-p
V. Installation Demonstration:
1. Disable mysql
Ps-ef | grep mysqld
Kill 3397 3801
2. Installation
Cat init3306
Sh init3306.sh
3. Start
Cat start3306.sh
Sh start3306.sh
Note: For mysql_install_db (mysql_install_db is generated by installing the rpm package), run the following command:
Rpm-pql MySQL-server-5.5.42-1.linux2.6.i386.rpm | grep install
Which mysql_install_db
4. View logs
Tail-100f/root/data/mysql3306/log/alert. log
When a data dictionary does not exist, it is automatically created ....
5. View Processes
Ps-ef | grep mysqld
Mysqld_safe is the parent process of mysqld
6. log on
Sh my3306.sh -- mysql-h127.0.0.1-uroot -- P3306
Vi. Script:
1. --- install 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. -- log on to mysql: my3306.sh
Mysql-h127.0.0.1-uroot-P3306-p
------ Under Research
Cd/root/data/mysql3306/data/mysql
Cd/root/data/mysql3306/data/performance_schema -- performance-related
Cd/root/data/mysql3306/data/test --- test Database
Cd/root/log/mysql3306/iblog/--- innodb's own data and logs
------ Understand my. cnf (multiple instances use ports for scoring)
Vi/root/data/mysql3306/my. cnf
[Client]
Port = 3306
Socket =/root/data/mysql3306/run/mysql. sock
[Mysql]
Port = 3306
Promprt =\\ u \\ 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
Relay-log =/root/log/mysql3306/binlog/relaylog
Innodb_force_recovery = 0
7. Operate mysql
1. log on 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 operation
(1) create a user
Method 1:
Insert into mysql. user (user, host, password) values ('mytest', 'localhost', password ('123 '));
Flush privilege;
Method 2: create user mystest @ '%' identified by '123 ';
(2) user authorization
Simple authorization
Grant all privileges on *. * to mytest @ localhost;
Grant insert, update, delete, select on *. * to mytest @ localhost;
Authorize and create a user
Grant all privileges on *. * to mytest @ localhost identified by '20140901'; -- create a user and cache the user,
(Equivalent to: insert into mysql. user, flush privilege)
Grant all privileges on *. * to mytest @ localhost; -- Object permission
Grant super on *. * to mytest @ '%'; -- system permission (supert is equivalent to dba permission in oracle)
3. Practice
Show databases; -- view all databases
Use mysql; -- switch to mysql database
Use tables; -- tables in the mysql database
Select user, host, password from mysql. user; ---- query all mysql users. This is created by mysql_install_db.
Grant all privilege on *. * to test_1 @ '%'; -- all indicates (select update, delete, alter admin, super_acl), First * user, second * object, % all Hosts
Mysql-h127.0.0.1-utest_1 ---- use the user created by grant to log on to mysql
Select user (); --- current user
Create database jianfeng; --- create a database (the database in mysql is similar to the schema in oracle
Create table user (id int) engine = innodb --- create a table;
Grant select on jianfeng. user to test_1 @ '%'; --- grant the query permission for the jianfeng. user table to the test_1 user
Insert into mysql. user (user, host, password) values ('test _ 2', '%', password ('000000'); -- create a user named test_2 in this way, you have no permission for a problem.
Flush privileges; --- refresh the user permissions of the mysql. user table to the memory.
Show master status \ G;
Change master to xxx;
Show processlist; --- view the connection of the current user, in the thread format (similar to v $ session in oracle)
4. drop table Processing
Rename table test_1 to test; (you can quickly switch back rename table test to test_1 ;)
Back up mysqldump: mysqldump-h127.0.0.1-uroot mydb gyj_t1>/tmp/gyj_t1. SQL
Drop table test;
5. Auto-increment primary key (it is best to define the primary key by yourself, and the system defaults to the global increment)
Create table test (id int primary key auto_increment, name varchar (100) 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-the original data will be moved and data needs to be copied
Alter table test add coll int;
7. Execution 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) Use dump to export data
Mysqldump-h127.0.0.1-uroot mydb gyj_t1>/tmp/xx. SQL
Drop table test;
Source/tmp/xx. SQL -- import data
(2) Use select to export data
Select * from test into outfile '/tmp/yy. SQL ';
9. Data Migration
(1) shutdown mode
Mysqldump/loadata
(2) non-stop mode
Physical: standby database (cascade 5.5 --> 5.6, backward compatible)
After the master database is read only, the slave database can digest the binlog transferred from the master database, and then switch the slave database to the master database.
Show variables like '% read % ';
Set global read_only = on;
Insert into test (name) values ('xx'); -- the root user cannot be used.
(3) small tables on different platforms: oracle ---> mysql
Script: synfull. pl
(4) migration of a large table on different platforms: Incremental migration
A. migrate all data
B. Upload the logs generated during the migration process
C. apply Increment
D. Switch the lock table
(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; -- clears 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, log first
11. Archiving
Flush logs;
Show master status;
Write ahead log. recover backup, duriably. undo acid mvcc
12. Parameters and statistics
Show variables; ---- Parameter
Show variables like '% bin % ';
Show status; ---- statistics
Show global status like '% insert % ';
Insert into test (name) values ('xxxxx ');
Show variables like '% default % ';
Set global default_storage_engine = myisam; --- the operation of the current session is not affected, and the newly established connection is affected.
Set session default_storage_engine = myisam; --- affects the operation of the current session
--- Connection pool
Max_connect
Min_connect
Max_idle
Time_out
Disconnect -- Release
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.
