 |
| MySQL Connect Command Line |
Login to MySQL server from the command line:-
In Windows, MySQL, as a matter of course, is introduced in the envelope %systemdrive%Program FilesMySQL. For instance, if the framework drive is C:, at that point the establishment catalog for MySQL server 5.5 would be C:Program Files MySQL MySQL Server 5.5
We can interface with MySQL server by running the mysql.exe situated in the receptacle envelope in the MySQL establishment registry.
In the event that you simply run MySQL without passing any contentions, you can interface with the server with the mysterious record. This works just if the unknown record isn't debilitated. When you introduce MySQL, as a matter of course, it empowers mysterious access.
Connect to MySQL as root:
You can connect to MySQL server by specifying the username and password in the command line arguments to mysql command. To connect as root-
mysql.exe -uroot -ppassword
You will be prompted for a password if -p option is skipped. If MySQL service is not running then the command would fail with the below error
C:>mysql.exe -u root -ppassword
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
You can create a desktop batch file to launch MySQL client as follows:
1. Make a content record and name it as mysql.bat.
2. Add the accompanying order to the mysql.bat document.
3. cmd/K "C:Program FilesMySQLMySQL Server 5.5binmysql.exe" - uroot - ppassword
4. Change the mysql.exe way according to your MySQL server form and introduce registry.
5. Spare the record. Presently onwards you can interface with the server by simply double tapping on the batch file.
An elective path is to make a work area alternate route with the objective as C:Program FilesMySQLMySQL Server 5.5binmysql.exe. In the wake of making the alternate route, open the properties and include - uroot - ppassword to the objective field.
The default establishment of MySQL makes an easy route in Start menu-> All Programs with the objective as beneath. You can utilize it as the objective for your work area easy route.
"C:Program FilesMySQLMySQL Server 5.5binmysql.exe" "--defaults-file=C:Program FilesMySQLMySQL Server 5.5my.ini" "-uroot" "-ppassword"
To connect to MySQL database on Windows through Command line, there are some steps to be followed:-
1. You have to begin the MySQL benefit, you can go to Start->Control Panel->System and Securities->Administrative Tools->Component Service->Services(local), at that point on the correct board, you can discover one administration name called MySQL and you should begin this administration
2. Go the MySQL establishment organizer, which is something like this : C:Program FilesMySQLMySQL Server 5.1. In this organizer, discover the receptacle envelope.
3. Open a charge comfort and change the working registry to C:Program FilesMySQLMySQL Server 5.1bin (You may change this as indicated by you MySQL establishment envelope). And after that write mysqld to begin the MySQL server. You can check the status of the mysql server by beginning the errand chief to check whether the mysqld procedure is available or you can type mysqladmin - u <username> - p <password> form to check whether it works or not.
4. Next, you have to begin the mysql customer, which is the mysql.exe document in an indistinguishable envelope from the mysqld.exe. On a similar order reassure, type mysql - u <username> - p <password> to interface with the server.
5. In the event that the association is fruitful, the mysql> provoke will appear. On the off chance that the association comes up short, you may check whether you write the right username and secret word.
6 After fruitful association, you can type SHOW DATABASES; to check the databases on the MySQL server.
Sometimes if we want to run MySQL but we don't have phpMyAdmin installed. What should we do?
On Windows we can run MySQL in following steps:
1. Open Console Window->Type "mysqld - u root". This implies we begin the mysql server with username root. This program will deal with every one of the correspondences between our support window and the genuine information;
2. In the wake of beginning the server, we have to utilize a customer to associate with the server and speak with it. the mysql program is appropriate for this. Next in the summon reassure, we compose "mysql - u root - p" to interface the server. As a matter of course, after the installation, the default watchword for client root is clear. On the off chance that you have changed the watchword, if it's not too much trouble enter your new secret key after the provoke "Enter watchword:";
3. In the event that all go well, congrats, you have effectively associated with the server and next you can utilize a few charges from this connect to deal with ensuing database preparing.
HOW TO REPAIR A MYSQL DATABASE THROUGH LINUX AND WINDOWS COMMAND LINE?
Repair MySQL Database through Linux Terminal(SSH):
1. Login to your Linux VPS utilizing Secure Shell (SSH) and interface with MySQL from the order line.
mysql -uUsername -p // Replace Username with your username.
2. Enter MySQL client secret word and hit Enter. You will see mysql provoke showing up. Presently, with a specific end goal to show all databases, type following command at mysql provoke.
show databases;
3. mysqlcheck empowers you to check databases without ceasing the whole MySQL benefit. - r contention is utilized to repair the ruined tables. mysqlcheck utility effectively chips away at both MyISAM and InnoDB database motors. Enter the following charge to repair MySQL database.
mysqlcheck -r [database] // Replace database name with your database name.
Repair MySQL Database through Windows Command Line:
1. Log in to your Windows VPS utilizing Remote Desktop.
2. Go to Start and find Command Prompt. Right snap Command Prompt and select Run as Administrator choice.
3. At windows summon incite explore to MySQL receptacle catalog as takes after.
cd C:Program FilesMySQLMySQL Server 5.5bin // Replace your MySQL bin directory path here.
4. Run the accompanying order to begin the MySQL Prompt.
mysql -u root -p
5. Enter MySQL root password when you are being asked.
6. You will see mysql provoke showing up. Presently, to show all databases, type following command at mysql prompt:
show databases;
7. Enter the accompanying summon to repair MySQL database.
mysqlcheck -r [database] // Replace database name with your database name.
1. At the order line, sign in to MySQL as the root client:
mysql -u root -p
2. Type the MySQL root password, and then press Enter.
3. To make a database client, type the accompanying charge. Supplant username with the client you need to make, and supplant watchword with the client's password:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
4. Type q to leave the mysql program.
5. To sign in to MySQL as the client you just made, type the accompanying command. Replace username with the name of the client you made in stage 3:
mysql -u username -p
6. Type the user's password, and then press Enter.
7. To create a database, type the following command. Replace dbname with the name of the database that you want to create:
CREATE DATABASE dbname;
8. To work with the new database, type the following command. Replace dbname with the name of the database you created in step 7:
USE dbname;
9. You can now work with the database. For example, the following commands demonstrate how to create a basic table named an example, and how to insert some data into it:
CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, 'Sample data' );
mysql is a basic SQL shell. It bolsters intelligent and non-intuitive utilize. At the point when utilized intelligently, the question comes about are displayed in an ASCII-table organization. At the point when utilized non-intuitively (for instance, as a channel), the outcome is introduced in tab-isolated configuration. The yield arrangement can be changed by utilizing order alternatives.
On the off chance that you have issues because of lacking memory for extensive outcome sets, utilize the - snappy choice. This powers mysql to recover comes about because of the server a column at any given moment instead of recovering the whole outcome set and buffering it in memory before showing it. This is finished by restoring the outcome set utilizing the mysql_use_result() C API work in the customer/server library instead of mysql_store_result().
Utilizing mysql is simple. Conjure it from the incite of your summon translator as takes after:
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 like this:
mysql db_name < script.sql > output.tab
How to specify which protocol to use when connecting to the mysqld server:-
The following is true for all MySQL and MariaDB command line clients:
You can force which protocol to be used to connect to the mysqld server by giving the protocol option one of the following values: tcp, socket, pipe or memory.
If the protocol is not specified, then the following happens:
1. In the event, that hostname isn't determined or hostname is localhost, at that point Unix attachments are utilized. Unused association parameters, (for example, port) will be disregarded.
2. In different cases (hostname is given and it's not localhost) at that point, a tcpip association through the port alternative is utilized.
Note that localhost is a unique esteem. Utilizing 127.0.0.1 isn't a similar thing. The last will interface with the mysqld server through tcpip
1. In the event that common memory-base-name is determined and hostname isn't indicated or hostname is localhost, at that point the association will occur through shared memory. Unused association parameters, (for example, port) will be disregarded.
2. On the off chance that common memory-base-name isn't indicated and hostname isn't determined or hostname is localhost, at that point the association will occur through windows named funnels.
3. Named funnels will likewise be utilized if the libmysql/libmariadb customer library identifies that the customer doesn't bolster tcpip.
4. In different cases then a tcpip association through the port alternative is utilized.
1. ?, ?
Synonym for `help'.
2. clear, c
Clear the current input statement.
3. connect, r
Reconnect to the server. Optional arguments are db and host.
4. delimiter, d
Set statement delimiter.
5. edit, e
Edit command with $EDITOR.
6. ego, G
Send command to mysql server, display result vertically.
7. exit, q
Exit mysql. Same as quit.
8. go, g
Send command to mysql server.
9. help, h
Display this help.
10. nopager, n
Disable pager, print to stdout.
11. notee, t
Don't write into outfile.
12. pager, P
Set PAGER [to_pager]. Print the query results via PAGER.
13. print, p
Print current command.
14. prompt, R
Change your mysql prompt. See prompt command for options.
15. quit, q
Quit mysql.
16. rehash, #
Rebuild completion hash.
17. source, .
Execute an SQL script file. Takes a file name as an argument.
18. status, s
Get status information from the server.
19. system, !
Execute a system shell command.
20. tee, T
Set outfile [to_outfile]. Append everything into given outfile.
21. use, u
Use another database. Takes database name as an argument.
22. charset, C
Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
23. warnings, W
Show warnings after every statement.
24. nowarning, w
Don't show warnings after every statement.
4. Run the accompanying order to begin the MySQL Prompt.
mysql -u root -p
5. Enter MySQL root password when you are being asked.
6. You will see mysql provoke showing up. Presently, to show all databases, type following command at mysql prompt:
show databases;
7. Enter the accompanying summon to repair MySQL database.
mysqlcheck -r [database] // Replace database name with your database name.
To create a MySQL database and user, follow these steps:-
1. At the order line, sign in to MySQL as the root client:
mysql -u root -p
2. Type the MySQL root password, and then press Enter.
3. To make a database client, type the accompanying charge. Supplant username with the client you need to make, and supplant watchword with the client's password:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
4. Type q to leave the mysql program.
5. To sign in to MySQL as the client you just made, type the accompanying command. Replace username with the name of the client you made in stage 3:
mysql -u username -p
6. Type the user's password, and then press Enter.
7. To create a database, type the following command. Replace dbname with the name of the database that you want to create:
CREATE DATABASE dbname;
8. To work with the new database, type the following command. Replace dbname with the name of the database you created in step 7:
USE dbname;
9. You can now work with the database. For example, the following commands demonstrate how to create a basic table named an example, and how to insert some data into it:
CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, 'Sample data' );
About the mysql command-line client:-
mysql is a basic SQL shell. It bolsters intelligent and non-intuitive utilize. At the point when utilized intelligently, the question comes about are displayed in an ASCII-table organization. At the point when utilized non-intuitively (for instance, as a channel), the outcome is introduced in tab-isolated configuration. The yield arrangement can be changed by utilizing order alternatives.
On the off chance that you have issues because of lacking memory for extensive outcome sets, utilize the - snappy choice. This powers mysql to recover comes about because of the server a column at any given moment instead of recovering the whole outcome set and buffering it in memory before showing it. This is finished by restoring the outcome set utilizing the mysql_use_result() C API work in the customer/server library instead of mysql_store_result().
Utilizing mysql is simple. Conjure it from the incite of your summon translator as takes after:
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 like this:
mysql db_name < script.sql > output.tab
How to specify which protocol to use when connecting to the mysqld server:-
The following is true for all MySQL and MariaDB command line clients:
You can force which protocol to be used to connect to the mysqld server by giving the protocol option one of the following values: tcp, socket, pipe or memory.
If the protocol is not specified, then the following happens:
Linux/Unix:
1. In the event, that hostname isn't determined or hostname is localhost, at that point Unix attachments are utilized. Unused association parameters, (for example, port) will be disregarded.
2. In different cases (hostname is given and it's not localhost) at that point, a tcpip association through the port alternative is utilized.
Note that localhost is a unique esteem. Utilizing 127.0.0.1 isn't a similar thing. The last will interface with the mysqld server through tcpip
Windows:
1. In the event that common memory-base-name is determined and hostname isn't indicated or hostname is localhost, at that point the association will occur through shared memory. Unused association parameters, (for example, port) will be disregarded.
2. On the off chance that common memory-base-name isn't indicated and hostname isn't determined or hostname is localhost, at that point the association will occur through windows named funnels.
3. Named funnels will likewise be utilized if the libmysql/libmariadb customer library identifies that the customer doesn't bolster tcpip.
4. In different cases then a tcpip association through the port alternative is utilized.
MySQL commands:-
There 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 ';'Command:
1. ?, ?
Synonym for `help'.
2. clear, c
Clear the current input statement.
3. connect, r
Reconnect to the server. Optional arguments are db and host.
4. delimiter, d
Set statement delimiter.
5. edit, e
Edit command with $EDITOR.
6. ego, G
Send command to mysql server, display result vertically.
7. exit, q
Exit mysql. Same as quit.
8. go, g
Send command to mysql server.
9. help, h
Display this help.
10. nopager, n
Disable pager, print to stdout.
11. notee, t
Don't write into outfile.
12. pager, P
Set PAGER [to_pager]. Print the query results via PAGER.
13. print, p
Print current command.
14. prompt, R
Change your mysql prompt. See prompt command for options.
15. quit, q
Quit mysql.
16. rehash, #
Rebuild completion hash.
17. source, .
Execute an SQL script file. Takes a file name as an argument.
18. status, s
Get status information from the server.
19. system, !
Execute a system shell command.
20. tee, T
Set outfile [to_outfile]. Append everything into given outfile.
21. use, u
Use another database. Takes database name as an argument.
22. charset, C
Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
23. warnings, W
Show warnings after every statement.
24. nowarning, w
Don't show warnings after every statement.
0 comments:
Post a Comment