Mysql note
多实例
https://www.cnblogs.com/xiaochina/p/7420063.html
add index
https://www.cnblogs.com/shijianchuzhenzhi/p/6263897.html
from w3cschool.com
1,modify the segment of table
alter table table_name add/drop var_name [var_type];
2,update
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
3,like==regexp
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
4,drop
DROP TABLE table_name ;
drop db_name;
5,create
mysql> CREATE TABLE tutorials_tbl(
-> tutorial_id INT NOT NULL AUTO_INCREMENT,
-> tutorial_title VARCHAR(100) NOT NULL,
-> tutorial_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY ( tutorial_id )
-> );
create db_name
6,delete
DELETE FROM table_name [WHERE Clause]
7,order by
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
8,INDEX
9,temp table
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
10,create clone table
mysql> SHOW CREATE TABLE tutorials_tbl \G;
mysql> CREATE TABLE `clone_tbl` (...);
mysql> INSERT INTO clone_tbl (tutorial_id,
-> tutorial_title,
-> tutorial_author,
-> submission_date)
-> SELECT tutorial_id,tutorial_title,
-> tutorial_author,submission_date,
-> FROM tutorials_tbl;
11,mysql info
SELECT VERSION( )
SELECT DATABASE( ) current dbname
SELECT USER( ) current user
SHOW STATUS
SHOW VARIABLES
12,AUTO_INCREMENT
reset table
insert table with id acsend
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
13,primary key,unique // The UNIQUE constraint uniquely identifies each record in a database table.
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name) //last_name not permit to be the same as first_name
);
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10)
UNIQUE (last_name, first_name) // INSERT IGNORE INTO person_tbl (last_name, first_name),insert record existed will not report err,either real insert it
);
14,export &LOAD
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
---
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
-> INTO TABLE mytbl (b, c, a); //specialize the order
---
mysqlimport dbname tablename.txt
15,Function
SELECT AVG(Price) AS PriceAverage FROM Products;
SELECT COUNT(*) AS NumberOfOrders FROM Orders;
SELECT FIRST(column_name) FROM table_name;
SELECT LAST(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;
SELECT MIN(column_name) FROM table_name;
SELECT SUM(column_name) FROM table_name;
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;
SELECT LCASE(CustomerName) AS Customer, City //extract characters from a text field
FROM Customers;
SELECT LEN(column_name) FROM table_name;
select round(2.555,2) from dual;
SELECT NOW() FROM dual;
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;
FOREIGN KEY constraint is used to prevent actions that would destroy links between tables
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
to be added...
mariadb多实例搭建
测试环境基于centos7.2,腾讯云实验室，学习搭建！
https://www.qcloud.com/developer
多实例mysql，能更加理解mysql安装的基本过程！及简单使用。。。
mariadb是mysql的衍生版（原作者退出mysql团队，主要是卖给oracle公司！自己想保持开源共享的特性。。。不得不说作者很屌）
Mariadb官方： https://mariadb.com/
00、yum测试#
[root@VM_94_232_centos ~]# yum search mariadb
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
=================================== N/S matched: mariadb ====================================
mariadb-bench.x86_64 : MariaDB benchmark scripts and data
mariadb-devel.i686 : Files for development of MariaDB/MySQL applications
mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb-embedded.i686 : MariaDB as an embeddable library
mariadb-embedded.x86_64 : MariaDB as an embeddable library
mariadb-embedded-devel.i686 : Development files for MariaDB as an embeddable library
mariadb-embedded-devel.x86_64 : Development files for MariaDB as an embeddable library
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-server.x86_64 : The MariaDB server and related files
mariadb.x86_64 : A community developed branch of MySQL
mariadb-test.x86_64 : The test suite distributed with MariaD
percona-xtrabackup.x86_64 : Online backup for InnoDB/XtraDB in MySQL, Percona Server and #很出名名的数据库备份，修复的公司，吊炸天
01、安装mysql#
yum install -y mariadb-server
mkdir /mvpbang/{data3333,data3334} #创建数据库文件存放位置
02、初始化数据库#
mysql_install_db --datadir=/mvpbang/data33343--user=mysql
mysql_install_db --datadir=/mvpbang/data3334 --user=mysql
chown -R mysql: /mvpbang/* #权限设置
Installing MariaDB/MySQL system tables in '/mvpbang/data3334' ... 170823 19:58:08 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 10956 ...OK Filling help tables... 170823 19:58:10 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 10965 ...OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER ! To do so, start the server, then issue the following commands: '/usr/bin/mysqladmin' -u root password 'new-password' '/usr/bin/mysqladmin' -u root -h VM_94_232_centos password 'new-password' Alternatively you can run: '/usr/bin/mysql_secure_installation' #安全设置（删除test库，禁止root远程登录） which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '/usr' ; /usr/bin/mysqld_safe --datadir='/mvpbang/data3334' You can test the MariaDB daemon with mysql-test-run.pl cd '/usr/mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Support MariaDB development by buying support/new features from MariaDB Corporation Ab. You can contact us about this at sales@mariadb.com. Alternatively consider joining our community based development effort: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
03、设置my_multi.cnf#
注意：本配置只在乎简单的多实例。对于对引擎等参数的设置，本文该不做介绍
show variables; #默认参数信息，可根据做对应的调整
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
[mysqld_multi]mysqld = /usr/bin/mysqld_safemysqladmin = /usr/bin/mysqladminuser = root #只能root管理[mysqld3333]socket = /tmp/mysql_3333.sockport = 3333pid-file = /mvpbang/data3333/3333.piddatadir = /mvpbang/data3333log = /mvpbang/log/mysql3333.loguser = mysql[mysqld3334]socket = /tmp/mysql_3334.sockport = 3334pid-file = /mvpbang/data3333/3334.piddatadir = /mvpbang/data3334log = /mvpbang/log/mysql3334.loguser = mysql |
04、启动关闭#
mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start | stop 3333,3334 #批量启动关闭
mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start | stop 3333
mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start | stop 3334 #单个进行操作
亦可以设置开机启动
vim /etc/rc.d/rc.local
mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start 3333,3334
05、设置root密码设置密码#
设置密码
mysqladmin -uroot -S /tmp/mysql_3333.sock password 123123; #sock套接字快速连接
mysqladmin -uroot -S /tmp/mysql_3334.sock password 123123;
mysql登录
mysql -uroot -p -S /tmp/mysql_3306.sock
06、常见命令参数#
mysqld_multi
[root@VM_94_232_centos ~]# mysqld_multi --help mysqld_multi version 2.16 by Jani Tolonen Description: mysqld_multi can be used to start, or stop any number of separate mysqld processes running in different TCP/IP ports and UNIX sockets. mysqld_multi can read group [mysqld_multi] from my.cnf file. You may want to put options mysqld=... and mysqladmin=... there. Since version 2.10 these options can also be given under groups [mysqld#], which gives more control over different versions. One can have the default mysqld and mysqladmin under group [mysqld_multi], but this is not mandatory. Please note that if mysqld or mysqladmin is missing from both [mysqld_multi] and [mysqld#], a group that is tried to be used, mysqld_multi will abort with an error. mysqld_multi will search for groups named [mysqld#] from my.cnf (or the given --defaults-extra-file=...), where '#' can be any positive integer starting from 1. These groups should be the same as the regular [mysqld] group, but with those port, socket and any other options that are to be used with each separate mysqld process. The number in the group name has another function; it can be used for starting, stopping, or reporting any specific mysqld server.
#使用方法 Usage: mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...] #report 运行状态报告 or mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...] The GNR means the group number. You can start, stop or report any GNR, or several of them at the same time. (See --example) The GNRs list can be comma separated or a dash combined. The latter means that all the GNRs between GNR1-GNR2 will be affected. Without GNR argument all the groups found will either be started, stopped, or reported. Note that syntax for specifying GNRs must appear without spaces. Options: These options must be given before any others: --no-defaults Do not read any defaults file --defaults-file=... Read only this configuration file, do not read the #常用语单实例 standard system-wide and user-specific files --defaults-extra-file=... Read this configuration file in addition to the #多实例配置文件 standard system-wide and user-specific files Using: --example Give an example of a config file with extra information. --help Print this help and exit. --log=... Log file. Full path to and the name for the log file. NOTE: If the file exists, everything will be appended. Using: --mysqladmin=... mysqladmin binary to be used for a server shutdown. Since version 2.10 this can be given within groups [mysqld#] Using: --mysqld=... mysqld binary to be used. Note that you can give mysqld_safe to this option also. The options are passed to mysqld. Just make sure you have mysqld in your PATH or fix mysqld_safe. Using: Please note: Since mysqld_multi version 2.3 you can also give this option inside groups [mysqld#] in ~/.my.cnf, where '#' stands for an integer (number) of the group in question. This will be recognised as a special option and will not be passed to the mysqld. This will allow one to start different mysqld versions with mysqld_multi. --no-log Print to stdout instead of the log file. By default the log file is turned on. --password=... Password for mysqladmin user. --silent Disable warnings. --tcp-ip Connect to the MySQL server(s) via the TCP/IP port instead of the UNIX socket. This affects stopping and reporting. If a socket file is missing, the server may still be running, but can be accessed only via the TCP/IP port. By default connecting is done via the UNIX socket. --user=... mysqladmin user. Using: root --verbose Be more verbose. --version Print the version number and exit.
