Shulou(Shulou.com)06/01 Report--

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

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:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.