What if mysql cannot be found

modify your PATH search-path environment variable, which specifies directories where the command interpreter looks for commands. Add to the PATH value the directory where mysql is installed. Then you can invoke mysql from any location by entering only its name, which eliminates pathname typing.

## please note ‘PATH’ is CASE sensitivity and must be in UPPERCASE ##

export PATH=$PATH:/path/to/dir1

or

PATH=$PATH:/path/to/dir1; export PATH

E.g. on Mac OSx

export PATH=$PATH:/usr/local/mysql/bin/
Set up user account , privilege

To access the MySQL for the first time, you need to log on as root user. You also need to specify some parameters instead of just type mysql:

% mysql -h localhost -u root -p
Enter password: dbpass

Each option is the single-dash “short” form:

  • -h and -u to specify the hostname and username,
  • -p to be prompted for the password.

There are also corresponding double-dash “long” forms: –host, –user, and –password.

Use them like this:

% mysql --host=localhost --user=cbuser --password
Enter password: cbpass

Because the default host is localhost, the same value we’ve been specifying explicitly, you can omit the -h (or –host) option from the command line:

% mysql -u cbuser -p

To see all options that mysql supports, use this command:

% mysql --help

set up a user account with privileges for accessing a database named testdb. The arguments to mysql include -h localhost to connect to the MySQL server running on the local host, -u root to connect as the MySQL root user, and -p to tell mysql to prompt for a password:

% mysql -h localhost -u root -p
Enter password: ******
mysql> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass'; mysql> GRANT ALL ON testdb.* TO 'cbuser'@'localhost'; Query OK, 0 rows affected (0.09 sec) mysql> quit Bye

To show the users in the mysql database:

mysql> select User,Host FROM mysql.user;

MySQL accounts and login accounts

The MySQL root user and the Unix root user are separate and have nothing to do with each other, even though the username is the same in each case. This means they very likely have different passwords.

Password complexity

 

The password complexity requirement can be three levels, you can check it by this command:

SHOW VARIABLES LIKE 'validate_password%';
Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

 

To set it:

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

 

 

Create a Database and a sample table

Use a CREATE DATABASE statement to create the database, a CREATE TABLE statement for each table, and INSERT statements to add rows to the tables.

mysql> CREATE DATABASE testdb;

Now that you have a database, you can create tables in it. First, select testdb as the default database:

mysql> USE testdb;

Then create a simple table:

mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);

And populate it with a few rows:

mysql> INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0)
mysql> INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

NULL indicates “unknown value.”

Verify that the rows were added to the limbs table by executing a SELECT statement:

mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing | legs | arms |
+--------------+------+------+
| human | 2 | 2 |
| insect | 6 | 0 |
| squid | 0 | 10 |
| fish | 0 | 0 |
| space alien | NULL | NULL |
+--------------+------+------+

to show the tables, use command:

show tables;

 

Mysqldump and mysqladmin

To generate a dump file named testdb.sql that contains a backup of the tables in the testdb database, execute mysqldump like this:

% mysqldump -h localhost -u cbuser -p tested > tested.sql
Enter password: cbpass

The mysqladmin program can perform operations that are available only to the MySQL root account. For example, to stop the server, invoke mysqladmin as follows:

% mysqladmin -h localhost -u root -p shutdown
Enter password: ← enter MySQL root account password here
MYSQL option

 

MySQL programs support option files:
• If you put an option in an option file, you need not specify it on the command line each time you invoke a given program.
• You can mix command-line and option-file options. This enables you to store the most commonly used option values in a file but override them as desired on the command line.

To avoid entering options on the command line each time you invoke mysql, put them in an option file for mysql to read automatically. Option files are plain-text files:

  • Under Unix, your personal option file is named .my.cnf in your home directory.  There are also site-wide option files that administrators can use to specify parameters that apply globally to all users. You can use the my.cnf file in the /etc or /etc/ mysql directory, or in the etc directory under the MySQL installation directory.
  • Under Windows, files you can use include the my.ini or my.cnf file in your MySQL installation directory (for example, C:\Program Files\MySQL\MySQL Server 5.6),  your Windows directory (likely C:\WINDOWS), or the C:\ directory.
  • On MAC OSX, /usr/local/mysql/support-files/my-default.cnf

The default port number is 3306 for TCP/IP connections. The pathname for the Unix domain socket varies, although it’s often /tmp/mysql.sock. To name the socket file pathname explicitly, use -S file_name or –socket=file_name.

Pages: 1 2