Mysql run sql file from terminal
About the mysql Command-Line Clientmysql (from MariaDB 10.4.6, also called mariadb) is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options. Show
If you have problems due to insufficient memory for large result sets, use the Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: mysql db_name Or: mysql --user=user_name --password=your_password db_name Then type an SQL statement, end it with “;”, \g, or \G and press Enter. Typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits. You can execute SQL statements in a script file (batch file) like this: mysql db_name < script.sql > output.tab From MariaDB 10.4.6, From MariaDB 10.5.2, Using mysqlThe command to use mysql Options
Option FilesIn addition to reading options from the command-line, The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line: In MariaDB 10.2 and later, Option Groups
How to Specify Which Protocol to Use When Connecting to the mysqld ServerYou can force which protocol to be used to connect to the If From MariaDB 10.6.1, a connection property specified via the command line (e.g. If multiple or no connection properties are specified via the command-line, then the following happens: Linux/Unix
Note that Windows
How to Test Which Protocol is UsedThe shell> mysql test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.2.2-MariaDB-valgrind-max-debug Source distribution Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test]> status; -------------- mysql Ver 15.1 Distrib 10.0.25-MariaDB, for Linux (x86_64) using readline 5.2 Connection id: 10 Current database: test Current user: monty@localhost ... Connection: Localhost via UNIX socket ... UNIX socket: /tmp/mysql-dbug.sock mysql CommandsThere are also a number of commands that can be run inside the client. Note that all text commands must be first on line and end with ';' The mysql_history FileOn Unix, the mysql client writes a record of executed statements to a history file. By
default, this file is named The .mysql_history file should be protected with a restrictive access mode because sensitive information might be written to it, such as the text of SQL statements that contain passwords. If you do not want to maintain a history file, first remove .mysql_history if it exists, and then use either of the following techniques:
shell> ln -s /dev/null $HOME/.mysql_history
You need do this only once. prompt CommandThe prompt command reconfigures the default prompt mysql TipsThis section describes some techniques that can help you use Displaying Query Results VerticallySome query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output: mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G *************************** 1. row *************************** msg_nro: 3068 date: 2000-03-01 23:29:50 time_zone: +0200 mail_from: Monty reply: mail_to: "Thimble Smith" <> sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes: Thimble> Hi. I think this is a good idea. Is anyone familiar Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my Thimble> TODO list and see what happens. Yes, please do that. Regards, Monty file: inbox-jani-1 hash: 190402944 1 row in set (0.09 sec) Using the --safe-updates OptionFor beginners, a useful startup option is When you use the SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000; The SET statement has the following effects:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val; UPDATE tbl_name SET not_key_column=val LIMIT 1;
To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the mysql --safe-updates --select_limit=500 --max_join_size=10000 Disabling mysql Auto-ReconnectIf the mysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even if mysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it: mysql> SET @a=1; Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t VALUES(@a); ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 1 row affected (1.30 sec) mysql> SELECT * FROM t; +------+ | a | +------+ | NULL | +------+ The @a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to have mysql terminate with an error if the connection has been lost, you can start the mysql client with the See Also
How do I run a .SQL File in MySQL terminal?use the MySQL command line client: mysql -h hostname -u user database < path/to/test. sql. Install the MySQL GUI tools and open your SQL file, then execute it. Use phpmysql if the database is available via your webserver.
How do I run a .SQL File in SQL command line?Run the script file. Open a command prompt window.. In the Command Prompt window, type: sqlcmd -S myServer\instanceName -i C:\myScript.sql.. Press ENTER.. How do I run a .SQL File?Running statements and files. Open the Files tool window (View | Tool Windows | Files) and double-click an SQL file. ... . Click the statement that you want to execute. ... . Press Ctrl+Enter or select Execute from the context menu.. How do I load a .SQL File in MySQL?Command line MySQL import. Type: mysql -u username -p database_name < file.sql.. The username refers to your MySQL username.. database_name refers to the database you want to import.. file. sql is your file name.. If you've assigned a password, type it now and press Enter.. |