Common operations of mysql
Mysql creates a database
Mysql > create database test
New users and passwords for Mysql
Mysql > grant all on *. * to ucenter@'%' identified by 'ucenter'; allows all hosts to log in
All represents all permissions; *. * represents all databases; ucenter represents user name;'% 'represents all hosts, excluding local host;' ucenter' represents user password, 2 'numbers are not passwords; identified by is encryption option
Mysql > flush privileges; update permissions table
Mysql > grant all on *. * to ucenter@'localhost' identified by 'ucenter'; allows local host to log in
Allow zoulongpu users to access the zoulongpu database and tables on all hosts
Grant all on zoulongpu.* to zoulongpu@'%' identified by 'zoulongpu'
Zoulongpu.* stands for zoulongpu database and its tables; *. * represents all databases
Specifies that the user has the right to find and modify the phblampDB database
Mysql > grant select,update on phplampDB.* to phplamp@localhost identified by '1234'
Select,update represents the user with query and modify permissions; phplampDB.* represents the database name and all the tables in it.
Mysql > flush privileges; update permissions table
Delete empty users in the user table
Mysql > delete from mysql.user where user =''
Modify mysql user password
Method 1. Mysql > update mysql.user set password=password ('ucenter') where user='root'
('ucenter') represents the user's password. Parentheses () and' 'are required, not passwords.
'root' represents the user to be modified. The' 'number is required.
Mysql > flush privileges; update permissions table
Method 2. [root@ccms-dzhkjr] # mysqladmin-u root-p password '456'
'456 'stands for the new password
Then the system will ask you for your current password, enter your current password and enter enter.
Modify mysql user name
Mysql > use mysql; mysql represents the database. All users are stored in mysql. When modifying, you need to enter the mysql database first.
Mysql > update user set user= "New user name" where user= "Old user name"
Mysql > flush privileges
Query mysql users
Mysql > select host,user,password from mysql.user
View all users in the MYSQL database
Mysql > SELECT DISTINCT CONCAT ('User:'', user,'''@''',host,''';') AS query FROM mysql.user
Method 1: go to the MYSQL installation directory and open the MYSQL configuration file my.ini or my.cnf to find max_connections=100 and modify it to restart MYSQL in the max_connections=1000 service.
Method 2: the maximum number of connections for MySQL is 100 client login by default: mysql-uusername-ppassword
Set the new maximum number of connections to 200:mysql > set GLOBAL max_connections=200
Show variables like'% conn%'; view the number of connections
Displays the currently running Query:mysql > show processlist
Show current status: mysql > show status
Exit the client: mysql > exit
View the current maximum number of connections: mysqladmin-uusername-ppassword variables
Source:
View permissions of the cactiuser user
Mysql > select * from mysql.user where user='cactiuser'\ G
'cactiuser' represents the user
View the local permissions table for fengmangang users
Mysql > show grants for fengmangang@'localhost'
Change fengmangang users can only access their own fengmangang database, not yufei database
Mysql > revoke all on yufei.fengmangang from 'fengmangang'@'localhost'
Check how many tables there are in a database
Mysql > SELECT COUNT (*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'database name' GROUP BY table_schema
List all databases
Mysql > show databases
View number of database connections
Mysql > show processlist
View the contents of the table
Select * from table_name
Delete database user
All users of Mysql > use mysql; are stored in mysql. When modifying, you need to enter the mysql database first.
Mysql > delete from user where user= "ucenter"
"ucenter" represents the user, "number must be written."
Delete the user's database
Mysql > drop database phplampDB
Delete data tabl
Mysql > drop table datasheet name
View the font encoding of the database
Mysql > show variables like'char%'
Query variables in the database that contain inno and lock
Mysql > show variables like'% inno%lock%'
Import a file into a table in the database
Load data infile'/ tmp/top_wwplugin_sku_2013050918_0_20130509194741.data file path 'into table tb_onsale_item_ property table name
Export database
Simple: Mysqldump-u user name-p database name > database export name such as (abc.sql)
Add parameters: mysqldump-u jira-- default-character-set=utf8-- no-autocommit-- skip-opt-- quick-- routines-- triggers-- events-- single-transaction-- create-options database name > database export name and path such as: / data/ abc.sql
Run the export database in the background
Nohup mysqldump-h 10.200.190.12-uadmin-pdatayun901 ccms > / data/ccms1.sql &
1. Export the entire database
Mysqldump-u user name-p password database name > exported file name
C:\ Users\ jack > mysqldump-uroot-pmysql sva_rec > e:\ sva_rec.sql
two。 Export a table, including table structure and data
Mysqldump-u user name-p password database name table name > exported file name
C:\ Users\ jack > mysqldump-uroot-pmysql sva_rec date_rec_drv > e:\ date_rec_drv.sql
3. Export a database structure
C:\ Users\ jack > mysqldump-uroot-pmysql-d sva_rec > e:\ sva_rec.sql
4. Export a table with only table structure
Mysqldump-u user name-p password-d database name table name > exported file name
C:\ Users\ jack > mysqldump-uroot-pmysql-d sva_rec date_rec_drv > e:\ date_rec_drv.sql
5. Import database
Common source commands
Go to the mysql database console
Such as mysql-u root-p
Mysql > use database
Then use the source command, followed by a script file (such as .sql used here)
Mysql > source d:wcnc_db.sql
Import sql file to mysql database
Mysql-h 10.200.188.90 host address-u root user-p huaat23 password database name
< /data/zbc.sql文件路径 查看数据库内正在进行的操作 mysql>Show processlist
Mysql > show binary logs
View database log files
Mysql > purge binary logs to 'mysql-bin.000026'
Delete database log 'mysql-bin.000026' represents the last file, which will be cleared before 26
Query the issue on jira that has been reopened (reopen)
Use jira
SELECT pkey, workflow_id, count (*), group_concat (START_DATE) FROM jiraissue LEFT JOIN OS_HISTORYSTEP ON jiraissue.WORKFLOW_ID = OS_HISTORYSTEP.ENTRY_ID where action_id = 3 group by pkey order by count (*) desc
Query all issue in July 2013
SELECT pkey,START_DATE FROM jiraissue LEFT JOIN OS_HISTORYSTEP ON jiraissue.WORKFLOW_ID = OS_HISTORYSTEP.ENTRY_ID where START_DATE like'% 2013-07% 'group by pkey
Query the issue that was reopen in July 2013.
SELECT pkey,START_DATE FROM jiraissue LEFT JOIN OS_HISTORYSTEP ON jiraissue.WORKFLOW_ID = OS_HISTORYSTEP.ENTRY_ID where action_id = 3 and START_DATE like'% 2013-07% 'group by pkey order by count (*) desc
Clean up the mysql-bin log
Purge binary logs to 'mysql-bin.000034'
'mysql-bin.000034' stands for cleaning up all files before 000034
Show variables like'% binary%'
Close the mysql-bin log
Set global log_bin=0; needs to be restarted
Mysql starts port 3307
Mysqld_multi-- defaults-extra-file=/etc/my.cnf start 2 &
Update table contents
Mysql > select * from cwd_directory_attribute
+-+
| | directory_id | attribute_name | attribute_value | |
+-+
| | 1 | user_encryption_method | atlassian-security |
| | 10000 | application.name | jira-hz |
| | 10000 | application.password | datayun0626 |
| | 10000 | com.atlassian.crowd.directory.sync.currentstartsynctime | NULL |
| | 10000 | com.atlassian.crowd.directory.sync.issynchronising | false |
| | 10000 | com.atlassian.crowd.directory.sync.lastdurationms | 5929 | |
| | 10000 | com.atlassian.crowd.directory.sync.laststartsynctime | 1406712326534 |
| | 10000 | crowd.server.url | http://jira.yunat.com:8095/crowd |
| | 10000 | crowd.sync.incremental.enabled | false |
| | 10000 | directory.cache.synchronise.interval | 3600 | |
| | 10000 | useNestedGroups | false |
+-+
11 rows in set (0.00 sec)
Mysql > update cwd_directory_attribute set attribute_value= "http://yunwan3.3322.org/crowd" where attribute_name=" crowd.server.url "
Mysql > create database jiradb character set utf8;Query OK, 1 row affected (0.02 sec)
Mysql
> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on jira.* TO 'jira'@'localhost' IDENTIFIED BY' jira'
Mysql > flush privileges
Delete a row in the table
Delete from cwd_group where group_name='Dev_Channel_Team'
Delete the row of Dev_Channel_Team in the cwd_group table
Delete tabl
Drop table name
We can make ip have the right to log in by modifying the host domain
Use mysql; # switch to mysql database
Update user set host='192.168.1.146' where user='root'
Flush privileges; # Refresh permissions
Modify MySQL user password
Update user set password=password ('aaaaaa') where user='root'; # changes a user's password through the password function
Flush privileges; # refresh permissions in memory
Add a new MySQL user and global related authorization
Format: grant [permission 1, permission 2, permission 3.] On *. * to userName@'host' identfied by 'password'; # the permissions added here are global
Common permissions are: all,select,drop,insert,delete,update,select
Grant common permissions on *. * [library. Table] to user name @ '192.168.1%' identified by 'password'
Mysql > use mysql
Mysql > grant all on *. * to test@ "192.168.1%" identified by '111111'
Mysql > # add user test at 192.168.1% network segment connection grants all permissions to all tables in all libraries and the password is' aaaaaa'
MySQL permission revocation
Revoke all on *. * from test@'192.168.1.%'; # revokes all global permissions of the test user
Authorization / recall against a library is recorded in the mysql.db table
Grant all on dbName.* to test@'192.168.1.%'; # authorizes the user to have all permissions on dbName
Revoke all on dbName.* from test@'192.168.1.%'; # withdraws the user's rights to the dbName library
Make more detailed authorization records for the table in the mysql.tables_priv table
Grant insert,update,select on dbName.tableName to test@'192.168.1.%'; # users can add, delete, modify, but not delete
Source:
