Some MySQL commands. Date : 29/01/2016 Version: 0.1 Remark : I did not wrote this note. I just collected some fragments of text and instructions from several public documents on the internet. ------------------------------------------------------------------------ Tools: ====== GUI : MySQL Administrator, MySQL Workbench Commandline: mysql, mysqladmin It is widely-used as the database component of LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack. MySQL was developed by Michael Widenius and David Axmark in 1994. Presently MySQL is maintained by Oracle (formerly Sun, formerly MySQL AB). ==================== Connect mysql: ==================== shell> mysql --host=localhost --user=myname --password=mypass mydb shell> mysql -h localhost -u myname -pmypass mydb there must be no space between -p or --password= and the password following it. The default is to send no password. shell> mysql --host=127.0.0.1 shell> mysql --protocol=TCP shell> mysql --host=remote.example.com shell> mysql --port=3306 --host=localhost default port is 3306. ==================== Connect mysqladmin: ==================== mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more. # mysqladmin -u root -p create TUTORIALS # mysqladmin password "my new password" # mysqladmin proc stat # mysqladmin -u root password YOURNEWPASSWORD # mysqladmin -u root -p version # mysqladmin -u root -p ping # mysqladmin -u root -ptmppassword status # mysqladmin -u root -p extended-status # mysqladmin -u root -p variables ==================== Show databases: ==================== mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ mysql> use mysql; mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | etc.. [root@host]# mysql -u root -p Enter password:****** mysql> use TUTORIALS; Database changed mysql> mysql> CREATE DATABASE database_name; ==================== Show processes: ==================== mysql> SHOW PROCESSLIST; mysql> SELECT User, Host, Password FROM mysql.user; mysql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'some-password'; 0 row(s) affected. sql> SELECT User, Host, Password FROM mysql.user; ==================== CREATE User: ==================== CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; CREATE USER dbadmin@localhost IDENTIFIED BY 'password'; Create User bob@'%' Identified By 'Astr0ngPhr@$e'; At this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell. Therefore, the first thing to do is to provide the user with access to the information they will need. GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'; GRANT all on db_name.* to 'db_user'@'localhost' identified by 'db_password'; GRANT select, insert, delete, update on db_name.* to 'db_user'@'localhost' identified by 'db_password'; View a List of MySQL Users: SELECT User,Host FROM mysql.user; Another way: ------------ root@host# mysql -u root -p Enter password:******* mysql> use mysql; Database changed mysql> INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y'); Query OK, 1 row affected (0.20 sec) mysql> FLUSH PRIVILEGES; Query OK, 1 row affected (0.01 sec) mysql> SELECT host, user, password FROM user WHERE user = 'guest'; +-----------+---------+------------------+ | host | user | password | +-----------+---------+------------------+ | localhost | guest | 6f8c114b58f2ce9e | +-----------+---------+------------------+ 1 row in set (0.00 sec) ==================== Example start/stop: ==================== First check if your MySQL server is running or not. You can use the following command to check this: ps -ef | grep mysqld If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command: root@host# cd /usr/bin ./safe_mysqld & Now, if you want to shut down an already running MySQL server, then you can do it by using the following command: root@host# cd /usr/bin ./mysqladmin -u root -p shutdown Enter password: ****** Another thread: --------------- /etc/init.d/mysqld start /etc/init.d/mysqld stop /etc/init.d/mysqld restart or like RedHat: service mysqld start service mysqld stop service mysqld restart errors: ------- Another MySQL daemon already running with the same unix socket. # mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak # service mysqld start A socket file doesnt actually contain data, it transports it.. It is a special, unusual type of file created with special system calls/commands. It is not an ordinary file. It is like a pipe the server and the clients can use to connect and exchange requests and data. Also, it is only used locally. Its significance is merely as an agreed rendezvous location in the filesystem. The socket file is created when the service is started and removed when the service is terminated. The location of the file is defined in /etc/my.cnf like so: [mysqld] socket=/var/run/mysql/mysql.sock Windows: -------- To start the mysqld server from the command line, you should start a console window (or “DOS window”) and enter this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" The path to mysqld may vary depending on the install location of MySQL on your system. You can stop the MySQL server by executing this command: C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqladmin" -u root shutdown If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted. =================== InnoDB: =================== InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM versions. =================== SELECT tables: =================== mysql> use TUTORIALS; Database changed mysql> SELECT * from tutorials_tbl +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 1 | Learn PHP | John Poul | 2007-05-21 | | 2 | Learn MySQL | Abdul S | 2007-05-21 | | 3 | JAVA Tutorial | Sanjay | 2007-05-21 | +-------------+----------------+-----------------+-----------------+ 3 rows in set (0.01 sec) Output to a file: ----------------- MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application . SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt' SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ==================== Multiple instances: ==================== It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.6 and one from MySQL 5.7, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases. Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances. Parameters can be set on the command line, in option files, or by setting environment variables. The primary resource managed by a MySQL instance is the data directory. Each instance should use a different data directory, the location of which is specified using the --datadir=dir_name option. Considering already there is an instance of MySQL on this machine. 1) Create separate data and log directories and assign permissions mkdir /var/lib/mysql2 chown -R mysql.mysql /var/lib/mysql2/ mkdir /var/log/mysql2 chown -R mysql.mysql /var/log/mysql2 2) Create a new mysql configuration file (Copy existing one and edit it) Edit this new configuration file and at least update the mysql port (default to 3306), the pid and socket to be different than the default ones, and also point the data and log folders to the ones created before 3) Initializing and starting mysql mysql_install_db --user=mysql --datadir=/var/lib/mysql2/ mysqld_safe --defaults-file=/etc/mysql2/my.cnf & 4) Connect to the new instance mysql -h 127.0.0.1 -P 3307 5) Stop mysql mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown Windows: you can specify the appropriate options on the command line or in an option file. It is more convenient to place the options in an option file, but it is necessary to make sure that each server gets its own set of options. To do this, create an option file for each server and tell the server the file name with a --defaults-file option when you run it. Suppose that you want to run one instance of mysqld on port 3307 with a data directory of C:\mydata1, and another instance on port 3308 with a data directory of C:\mydata2. Use this procedure: 1. Make sure that each data directory exists, including its own copy of the mysql database that contains the grant tables. 2. Create two option files. For example, create one file named C:\my-opts1.cnf that looks like this: [mysqld] datadir = C:/mydata1 port = 3307 Create a second file named C:\my-opts2.cnf that looks like this: [mysqld] datadir = C:/mydata2 port = 3308 3. Use the --defaults-file option to start each server with its own option file: C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts1.cnf C:\> C:\mysql\bin\mysqld --defaults-file=C:\my-opts2.cnf Each server starts in the foreground (no new prompt appears until the server exits later), so you will need to issue those two commands in separate console windows. To shut down the servers, connect to each using the appropriate port number: C:\> C:\mysql\bin\mysqladmin --port=3307 --host=127.0.0.1 --user=root --password shutdown C:\> C:\mysql\bin\mysqladmin --port=3308 --host=127.0.0.1 --user=root --password shutdown Servers configured as just described permit clients to connect over TCP/IP. If your version of Windows supports named pipes and you also want to permit named-pipe connections, specify options that enable the named pipe and specify its name. Each server that supports named-pipe connections must use a unique pipe name. For example, the C:\my-opts1.cnf file might be written like this: [mysqld] datadir = C:/mydata1 port = 3307 enable-named-pipe socket = mypipe1 Modify C:\my-opts2.cnf similarly for use by the second server. Then start the servers as described previously. A similar procedure applies for servers that you want to permit shared-memory connections. Enable such connections with the --shared-memory option and specify a unique shared-memory name for each server with the --shared-memory-base-name option.