Mysql commands used in Linux ubuntu (rpm)

2011-05-24  来源:本站原创  分类:Database  人气:109 

Mysql commands used in Linux

1.linux start the mysql command:
mysqladmin start
/ Ect / init.d / mysql start (in front of the mysql installation path)

2.linux to restart the mysql command:
mysqladmin restart
/ Ect / init.d / mysql restart (in front of the mysql installation path)

3.linux shut down the mysql command:
mysqladmin-u root-p password shutdown
/ Ect / init.d / mysql shutdown (in front of the mysql installation path)

4 Connect the machine to mysql:
Into the directory mysql \ bin, then type the command mysql-uroot-p, enter after the password.
Exit mysql command: exit (Enter)

5. Change mysql password:
mysqladmin-u username-p password old password new password or enter the mysql command line SET PASSWORD FOR 'username' @ 'host' = PASSWORD ('password');

GRANT USAGE ON *.* TO 'username' @ 'host' IDENTIFIED BY 'biscuit';

Modify their own password SET PASSWORD = PASSWORD ('biscuit');

6 to add new users. (Note: mysql environment after the command with a semicolon as a command terminator)

grant all privileges on *.* to username @ '%' identified by 'password' with grant option;

flush privileges; (refresh permissions)
grant select on database .* to username @ log host identified by "password"
Such as adding a user test password is 123, so that he can log on any host, and all databases have query, insert, update, delete permissions. First, the root user to connect into the mysql, and then type the following command:
grant select, insert, update, delete on *.* to "Identified by" 123 ";

7. Skip authorized to access mysql

mysqld_safe - user = mysql - skip-grant-tables - skip-networking &

Second, the mysql database in operation must first log into the mysql, the operations are carried out in the mysql prompt, and end each command with a semicolon

1, shows the list of databases.
show databases;
2 shows the data in the database table:
use mysql; / / open the library
show tables;
3 shows the structure of the data sheet:
describe table;
4, building a database:
create database database name;
5, the construction of the table:
use library name;
create table table name (field set list);
6, delete the library and delete tables:
drop database database name;
drop table table name;
7, recorded in the table empty:
delete from table name;
8, show records in the table:
select * from table name;

9, code changes if you want to change the mysql encoding format:
Mysql start time, mysqld_safe command line to
- Default-character-set = gbk

If you want to change the encoding format of a database: the mysql prompt, enter the command
alter database db_name default character set gbk;

Third, data import and export

1, the Chinese text data to the database format of the data should be consistent: use the tab key between fields separated data, null values ​​are used instead. Example:
1 name duty 2006-11-23
Data into the command load data local infile "filename" into table table name;

2, export the database and tables
mysqldump - opt news> news.sql (the news all the tables in the database backup to news.sql file, news.sql is a text file, the file name either to take.)
mysqldump - opt news author article> author.article.sql (database news article in the author table and the table back to author.article.sql file, author.article.sql is a text file, the file name either to take.)
mysqldump - databases db1 db2> news.sql (dbl and db2 database backup to news.sql file, news.sql is a text file, the file name either to take.)
mysqldump-h host-u user-p pass - databases dbname> file.dump
Is the host name of the user, the password of the database dbname will pass into the file file.dump
mysqldump - all-databases> all-databases.sql (all to the all-databases.sql database backup file, all-databases.sql is a text file, the file name either to take.)

3, import data
mysql <all-databases.sql (into database)

mysql-u root-p fukai-force <dmc010003_db.myisam.sql (forced into)
mysql> source news.sql; (in the mysql command executed, the table can be imported)

MySQLimport common option description:

-D or - delete the new data into the data table to delete the data before all of the information in the data table

-F or - force regardless of whether an error is encountered, MySQLimport forces continue to insert data

-I or - ignore MySQLimport skip or ignore the only keyword that has the same line, the data in the import file will be ignored.

-L or-lock-tables locks tables before the data is inserted, thus prevented, you update the database, the user's query and update affected.

-R or-replace this option with the-i option does the opposite; for this option will have the same unique key on behalf of the record.

- Fields-enclosed-by = char data in the specified text file to record what is enclosed, and in many cases the data in double quotes. By default data is not enclosed in the character.

- Fields-terminated-by = char all the data specified separator between the values ​​in the period-separated file, the separator is a period. You can use this option to specify the separator between data.

The default delimiter is a tab character (Tab)

- Lines-terminated-by = str This option specifies the text file data between the rows and rows separated by a string or character. By default MySQLimport the newline as a line separator.

You can choose to use a string to replace a single character:

A new line or a carriage return.

MySQLimport commands commonly used options as well-v show version (version),-p prompts for password (password)

Example: Importing a comma delimited file

Lines in a file record format is this:

"1", "ORD89876", "1 Dozen Roses", "19991226"

Our task is to put this file inside the data into a database Meet_A_Geek the table Orders, we use this command:

bin / MySQLimport-prl-fields-enclosed-by = "-fields-terminated-by =, Meet_A_Geek Orders.txt

First, connect to MySQL
Format: mysql-h host address-u username-p password

1, Example 1: Connect to the machine on MYSQL.

First, open a DOS window, then enter the directory mysqlbin, and then type the command mysql-uroot-p, enter the password after the prompt you lose, If you have just installed MYSQL, there is no super-user root password, so you can directly enter into the in the MYSQL, MYSQL prompt is: mysql>.

2, Example 2: connect to remote host on MYSQL. Assuming the remote host's IP is: 110.110.110.110, user name is root, password is abcd123. Type the following command:

mysql-h110.110.110.110-uroot-pabcd123

(Note: u and the root can not add space, the other is the same)

3, exit MYSQL command: exit (Enter).

mysql commands used to maintain

1, show global status; The MySQL server is running all kinds of state values

2, show variables; query MySQL server configuration information statement

3, see slow queries

show variables like '% slow%';

show global status like '% slow%';

4, the maximum number of connections

show variables like 'max_connections'; MySQL server maximum number of connections

show global status like 'Max_used_connections'; the maximum number of connections the server response

5, see table structure

desc Tablename;

describe Tablename;

show columns from Tablename;

show create table Tablename;

ALTER TABLE - change the table properties
ALTER TABLE - change the table attribute fields:

alter table `user_movement_log`
Add column GatewayId int not null default 0 AFTER `Regionid` (in which fields to append)

Delete fields:

alter table `user_movement_log` drop column Gatewayid

Adjust the field order:

ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID

相关文章
  • Mysql commands used in Linux ubuntu (rpm) 2011-05-24

    Mysql commands used in Linux 1.linux start the mysql command: mysqladmin start / Ect / init.d / mysql start (in front of the mysql installation path) 2.linux to restart the mysql command: mysqladmin restart / Ect / init.d / mysql restart (in front of

  • Linux, mysql commonly used commands ubuntu (rpm) 2011-05-24

    Linux, mysql commonly used commands 1.linux start the mysql command: mysqladmin start / Ect / init.d / mysql start (in front of the mysql installation path) 2.linux next restart mysql command: mysqladmin restart / Ect / init.d / mysql restart (in fro

  • Linux(Ubuntu)下MySQL的安装与配置 2015-03-27

    在Linux下MySQL的安装,我一直觉得挺麻烦的,因为之前安装时就是由于复杂的配置导致有点晕.今天,需要在Linux下用Qt连接MySQL.遂安装配置了一把. 1)首先检查系统中是否已经安装了MySQL 在终端里面输入 sudo netstat -tap | grep mysql 若没有反映,没有显示已安装结果,则没有安装.若如下显示,则表示已经安装 2)如果没有安装,则安装MySQL. 在终端输入 sudo apt-get install mysql-server mysql-client

  • MySQL configuration file under linux to explain [Reprinted 2010-07-24

    MySQL configuration file Source: [url] http://www.linuxidc.com/Linux/2009-04/19285.htm [/ url] MySQL to the latest products are actually exceeded by the carrying Red Hat Enterprise Linux version. However, the stability and open source, but very impor

  • Linux (Ubuntu, CentOs) Common Command Summary 2011-02-15

    Linux (Ubuntu, CentOs) Common Command Summary To help other novice like me can get started quickly, not "command" the fear, order the following special commands used for inspection: (The following experience mainly from Ubuntu, Red Hat and CentO

  • Install AMP from source under Linux (Ubuntu) 2011-04-12

    Install AMP from source under Linux (Ubuntu) 2011-02-05 sunny Saturday Install AMP from source under Linux (Ubuntu) Installation Apache2.2.17 Follow the instruction in the offical website, it should work: Overview for the impatient Download $ Lynx ht

  • MySQL安装与应用[Linux下的安装与配置] 2012-03-09

    Linux下的安装与配置 如果所安装的Linux系统没有内置的MySQL,笔者建议在Linux中使用RPM包来安装MySQL,同样这也是MySQL官方提供的建议.笔者接触最多的Linux系统是Radhat的"近亲":CentOS,由于CentOS较新的版本都内置了MySQL,因此在安装系统时就将MySQL安装并注册为系统服务,省去了不少工作量,此处也就不对Linux下的安装做太多陈述,仅提供部分资料供参考. 注意: MySQL至少需要Linux 2.0版本. 以下是网友提供的CentO

  • Linux / Ubuntu chmod command using the format and methods Detailed 2010-11-04

    Transfer from: http://www.ownlinux.cn/2008/03/14/ubuntu-linux-chmod/ Source files in the Ubuntu Linux installation using the software frequently used chmod command to change the permissions to execute when the installation permissions. Ubuntu Linux c

  • Linux ubuntu lamp install and configure the environment phpmyadmin 2010-12-30

    Indicate Reprinted: http://farlee.info/archives/linux-ubuntu-lamp-apache-mysql-php-phpmyadmin-install-configuration.html of FarLee PHP development and server operating environment of choice LAMP combination, that Linux + Apache + Mysql + Php / Perl /

  • You do not appear to be using the NVIDIA X driver on Linux Ubuntu 2011-07-20

    After a regular upgrade on Linux Ubuntu Lucid 9.10, I was greeted with the following message: You do not appear to be using the NVIDIA X driver. Please edit your X configuration file (just run `nvidia-xconfig` as root), and restart the X server. I tr

  • linux ubuntu fedora kernel 2011-09-02

    linux ubuntu fedora kernel Programming information post by boats on2011-6-6 12:46 Monday 1. Linux system, a common method of compiling kernel kernel code in the README file for the compilation process has a very detailed description of the method des

  • linux ubuntu lamp 安装配置环境 +phpmyadmin (-) 2011-08-20

    Linux ubuntu lamp安装配置环境phpmyadmin PHP开发和服务器运行环境首选LAMP组合,即Linux+Apache+Mysql+Php/Perl/Python,能最优化服务器性能.如何在本地电脑Ubuntu 中安装和配置LAMP环境搭建?Ubuntu9.10本身就是基于Linux内核,所以Linux是现成的了.使用Ubuntu LAMP Server软件包可以很简单地实现Linux下Apache,Mysql和Php的统一安装和配置,也不再需要一个一个来安装配置了. Ubu

  • uninstall linux ubuntu windows dual system to set the default boot xp 2010-07-20

    In the Windows system after installing linux ubuntu dual system, sometimes need to uninstall Ubuntu linux system, or to boot the default boot Linux ubuntu changed to start the windows XP system, then pay attention to repair the boot partition (DOS co

  • Changes on the mysql root password under linux problems encountered 2010-08-03

    Changes on the mysql root password under linux processes, can be found online a lot, probably is: # Killall mysqld / / interrupt the process currently running mysql # Mysqld_safe - skip-grant-tables & / / start in Safe Mode # Mysql / / no direct acce

  • mysql commands in the interactivity 2010-08-06

    mysql> \ h For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, vis

  • Under the commonly used commands vi editor linux command Daquan 2010-10-16

    Under the commonly used commands vi editor linux command Daquan Text editor is the most commonly used of all computer systems as a tool. Under UNIX editor has ex, sed, and vi and so on, which is the most widely used vi, the vi command range, the foru

  • SecureCRT connection Linux - Ubuntu 2010-12-07

    SSH seen as an enhanced version of telnet, telnet password and information are not encrypted, but ssh is encrypted. Want to use SecureCRT to connect Linux - Ubuntu, need the support of SSH service, many Linux systems, when installed SSH service is no

  • MYSQL Installation Deployment Guide Linux platform 2010-12-21

    MYSQL Installation Deployment Guide Linux platform

  • commonly used commands in the svn ubuntu 2010-12-31

    commonly used commands in the svn ubuntu 2008-12-20 20:04 svn command usually have help, you can query through the following ways: $ Svn help Know the sub-command, but do not know the usage of sub-commands, you can query: $ Svn help add Developers fr

  • linux (ubuntu) linked to each other between the disk and disk linux mount windows 2011-08-04

    Original article, please indicate the source Linux (ubuntu) mounted between the disk 1 two machines to be installed nfs sudo apt-get install nfs-kernel-server 2 hanging in the command sudo mount -t nfs 192.168.1.220:/home/.../.../ /home/.../.../ Firs