您的位置:金沙游乐场85155 > 大数据库 > mysql -u root -p 解释

mysql -u root -p 解释

发布时间:2019-11-20 21:18编辑:大数据库浏览(181)

    for password options, the password value is optional:

           ·  --xml, -X

    一般在使用中,我们会省略-h参数,mysql会自动默认为本地连接

              Use charset as the default character set. See Section 7.1, “The
              Character Set Used for Data and Sorting”. If not specified,
              mysqldump from MySQL 4.1.2 or later uses utf8, and earlier versions
              use latin1.

    如果你明确指定了-p或者--password的值,那么-p或者--password和密码值之间是不能有空格的。

              Examples:

     

      这个选项将会在每一个表的前面加上DROP TABLE IF EXISTS语句,这样可以保证导回MySQL数据库的时候不会出错,因为每次导回的时候,都会首先检查表是否存在,存在就删除

      If you use a -p or --password option and specify the password value, there must be no space between -p or --password= and the password following it.

           ·  --no-create-db, -n

    当然命令行连接数据库还有其它参数,这里主要介绍几个经常使用的,其它请参考https://dev.mysql.com/doc/refman/5.5/en/connecting.html

              Write dump output as well-formed XML.

    对于MySQL,第一个非选项参数被当作默认数据库的名称。如果没有这样的选项,MySQL就不会选择默认数据库。

           ·  --protocol={TCP | SOCKET | PIPE | MEMORY}

    mysql -h localhost -u myname -ppassword mydb

    复制代码 代码如下:

    For mysql, the first nonoption argument is taken as the name of the default database. If there is no such option, mysql does not select a default database.

    使用mysqldump
    mysqldump -u root -p your-new-password databasename [tablename] > db.sql

      If you use a -p or --password option but do not specify the password value, the client program prompts you to enter the password. The password is not displayed as you enter it. This is more secure than giving the password on the command line. Other users on your system may be able to see a password specified on the command line by executing a command such as ps auxw

           ·  --comments[={0|1}]

    mysql --host=localhost --user=myname --password=password mydb

      这个选项使得mysqldump命令给每一个产生INSERT语句加上列(field)的名字。当把数据导出导另外一个数据库时这个选项很有用。

    使用此命令首先确保你的mysql运行环境已经搭建好

    mysqldump按条件导入:

    这是我从mysql官方文档拷过来的内容

           shell> mysqldump [options] db_name [tables]
           shell> mysqldump [options] --databases DB1 [DB2 DB3...]
           shell> mysqldump [options] --all-databases

    第一个是全拼,第二个是第一个的缩写

              This option is useful for dumping large tables. It forces mysqldump
              to retrieve rows for a table from the server a row at a time rather
              than retrieving the entire row set and buffering it in memory before
              writing it out.

    具体就是:对于password选项,此选项是可选的

              Deprecated, renamed to --lock-all-tables in MySQL 4.1.8.

    还有一点就是各个参数之间是否有空格的问题,-u后面可以有也可以省略空格,对于-p后面的参数我要单独说一下

           ·  --tables

    也就是说在命令行中,你的mysql密码和-p或者--password参数之间有空格,mysql会认为你输入的是登录mysql后自动选择的数据库,而不是你所期望的密码

           The significance of this behavior is that if you dump and restore the
           table, the new table has contents that differ from the original
           contents. This problem is fixed as of MySQL 4.1.2; you cannot insert
           inf in the table, so this mysqldump behavior is only relevant when you
           deal with old servers.

    这是客户端连接mysql服务器的指令,比较全的写法是下面两种

              Include all MySQL-specific table options in the CREATE TABLE
              statements. Before MySQL 4.1.2, use --all instead.

    如果你使用了-p或者--password选项但是没有给出password值,客户端程序提示您输入密码。

              Override the --databases or -B option. All arguments following the
              option are regarded as table names.

    mysqldump导出表:

      -f or -force 使用这个选项,即使有错误发生,仍然继续导出

    AUTHOR
           MySQL AB (http://www.mysql.com/).  This software comes with no
           warranty.

      选项/Option 作用/Action Performed

    mysqldump工具有大量的选项,部分选项如下表:

           It is possible to dump several databases with one command:

              When using this option, you should keep in mind that only InnoDB
              tables are dumped in a consistent state. For example, any MyISAM or
              HEAP tables dumped while using this option may still change state.

           ·  --result-file=file, -r file

           ·  --host=host_name, -h host_name

              Lock all tables across all databases. This is achieved by acquiring
              a global read lock for the duration of the whole dump. This option
              automatically turns off --single-transaction and --lock-tables.
              Added in MySQL 4.1.8.

           For more information, please refer to the MySQL Reference Manual, which
           may already be installed locally and which is also available online at
           http://dev.mysql.com/doc/.

              Direct output to a given file. This option should be used on
              Windows, because it prevents newline ‘n' characters from being
              converted to ‘rn' carriage return/newline sequences.

    mysqldump -u用户名 -p密码 -h主机 数据库 a –where “条件语句” –no-建表> 路径
    mysqldump -uroot -p1234 dbname a –where “tag='88′” –no-create-info> c:a.sql

      -F or -flush-logs 使用这个选项,在执行导出之前将会刷新MySQL服务器的log.

           ·  --tab=path, -T path

    mysqldump备份:

              For each table, surround the INSERT statements with /*!40000 ALTER
              TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
              ENABLE KEYS */; statements. This makes loading the dump file into a
              MySQL 4.0 or newer server faster because the indexes are created
              after all rows are inserted. This option is effective for MyISAM
              tables only.

    NAME
           mysqldump - a database backup program

           ·  --first-slave, -x

           ·  --compact

              Note: This option should be used only when mysqldump is run on the
              same machine as the mysqld server. You must have the FILE privilege,
              and the server must have permission to write files in the directory
              that you specify.

           ·  --master-data[=value]

              Dump several databases. Normally, mysqldump treats the first name
              argument on the command line as a database name and following names
              as table names. With this option, it treats all name arguments as
              database names.  CREATE DATABASE IF NOT EXISTS db_name and USE
              db_name statements are included in the output before each new
              database.

           shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

           ·  --where=�����where-condition�����, -w �����where-condition�����

           ·  --delayed-insert

           You can read the dump file back into the server like this:

           If you want to dump all databases, use the --all-databases option:

           ·  --lock-tables, -l

    参考国外网站

           ·  --databases, -B

           ·  --default-character-set=charset

              The --single-transaction option was added in MySQL 4.0.2. This
              option is mutually exclusive with the --lock-tables option, because
              LOCK TABLES causes any pending transactions to be committed
              implicitly.

              Dump only records php/select">selected by the given WHERE condition. Note that
              quotes around the condition are mandatory if it contains spaces or
              characters that are special to your command interpreter.

           ·  --no-create-info, -t

              Write a debugging log. The debug_options string is often
              ´d:t:o,file_name'.

           ·  --allow-keywords

              Insert rows using INSERT DELAYED statements.

              Add SET NAMES default_character_set to the output. This option is
              enabled by default. To suppress the SET NAMES statement, use
              --skip-set-charset. This option was added in MySQL 4.1.2.

           ·  --add-drop-database

              Display version information and exit.

           For point-in-time recovery (also known as “roll-forward”, when you need
           to restore an old backup and replay the changes which happened since
           that backup), it is often useful to rotate the binary log (see
           Section 8.4, “The Binary Log”) or at least know the binary log
           coordinates to which the dump corresponds:

           ·  net_buffer_length

           ·  max_allowed_packet

           ·  --add-locks

           If you run mysqldump without the --quick or --opt option, mysqldump
           loads the whole result set into memory before dumping the result. This
           probably is a problem if you are dumping a big database. As of MySQL
           4.1, --opt is enabled by default, but can be disabled with --skip-opt.

           If you are using a recent copy of the mysqldump program to generate a
           dump to be reloaded into a very old MySQL server, you should not use
           the --opt or -e options.

              Quote database, table, and column names within ‘‘' characters. If
              the server SQL mode includes the ANSI_QUOTES option, names are
              quoted within ‘"' characters. As of MySQL 4.1.1, --quote-names is on
              by default. It can be disabled with --skip-quote-names, but this
              option should be given after any option such as --compatible that
              may enable --quote-names.

    复制代码 代码如下:

              On a master replication server, delete the binary logs after
              performing the dump operation. This option automatically enables
              --first-slave before MySQL 4.1.8 and enables --master-data
              thereafter. It was added in MySQL 3.23.57 (for MySQL 3.23) and MySQL
              4.0.13 (for MySQL 4.0).

              The MySQL username to use when connecting to the server.

           shell> mysqldump --all-databases --single-transaction > all_databases.sql

      --add-drop-table

           ·  --single-transaction

           There are three general ways to invoke mysqldump:

           ·  --set-charset

              Use complete INSERT statements that include column names.

    mysqldump还原:

           ·  --version, -V

              Verbose mode. Print out more information on what the program does.

              Compress all information sent between the client and the server if
              both support compression.

           The simultaneous use of --master-data and --single-transaction works as
           of MySQL 4.1.8. It provides a convenient way to make an online backup
           suitable for point-in-time recovery if tables are stored in the InnoDB
           storage engine.

              Surround each table dump with LOCK TABLES and UNLOCK TABLES
              statements. This results in faster inserts when the dump file is
              reloaded. See Section 2.13, “Speed of INSERT Statements”.

           ·  --debug[=debug_options], -# [debug_options]

              Do not write any row information for the table. This is very useful
              if you want to get a dump of only the structure for a table.

           shell> mysqldump --opt db_name > backup-file.sql

           mysqldump is also very useful for populating databases by copying data
           from one MySQL server to another:

              Continue even if an SQL error occurs during a table dump.

           If you do not name any tables or use the --databases or --all-databases
           option, entire databases are dumped.

           ·  --skip-comments

           ·  --complete-insert, -c

              The maximum size of the buffer for client/server communication. The
              value of the variable can be up to 16MB before MySQL 4.0, and up to
              1GB from MySQL 4.0 on.

              This option issues a BEGIN SQL statement before dumping data from
              the server. It is useful only with transactional tables such as
              InnoDB and BDB, because then it dumps the consistent state of the
              database at the time when BEGIN was issued without blocking any
              applications.

           ·  --password[=password], -p[password]

           shell> mysql db_name < backup-file.sql

    mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

           For more information on making backups, see Section 6.1, “Database
           Backups”.

           shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:mysql -u root -p 解释

    关键词:

上一篇:sql最简单的查询语句

下一篇:没有了