Learn Online Program
Today Use Tomarrow Use

Linux » Database

Your content goes here...

Start the database:

Start the database: /etc/rc.d/init.d/mysqld start 
(The script will run /usr/bin/mysql_install_db to create a default database in /var/lib/mysql/mysql/ if the mysql init script has never been run before. The install script will not be run again as long as the default database directory exists.) 
The database executes as user mysqld and group mysqld.

Notes:

  • Init script will create and initialize the database with the command: /usr/bin/mysql_install_db 
    Creates system tables in /var/lib/mysql/mysql/ 
    Only executes the first time the MySQL init script is run. One may also initialize the database manually using this command.
  • Files for databases located in: /var/lib/mysql/
  • Default config file installed by RPM: /etc/my.cnf 
    (Ubuntu: /etc/mysql/my.cnf)
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

Post installation:

  1. Admin user id: root 
    Default password: blank

    The first task is to assign a password:

    [prompt]$  mysqladmin -u root password 'new-password' 
    Note: the following SQL commands will also work:
    mysql>  USE mysql;
    mysql> UPDATE user SET Password="PASSWORD('new-password') WHERE user='root'; mysql> FLUSH PRIVILEGES;
  2. Create a database: (Creates directory /var/lib/mysql/bedrock)
    [prompt]$  mysqladmin -h localhost -u root -ppassword  create bedrock
    (or use SQL command: CREATE DATABASE bedrock;) 
    Show all mysql databases: mysqlshow -u root -ppassword
  3. Add tables, data, etc: 
    Connect to database and issue the following SQL commands:
    [prompt]$  mysql -h localhost -u root -ppassword
     ... 
    mysql>  show databases;
                    -- List all databases in MySQL. 
    +----------+
    | Database |
    +----------+
    | bedrock  |
    | mysql          |
    | test              |
     +----------+ 
    mysql>  use bedrock;       -- Specify database to connect to. Also refers to path: /var/lib/mysql/bedrock 
    mysql>  create table employee (Name char(20),Dept char(20),jobTitle char(20));
    mysql> DESCRIBE employee; 
             -- View table just created. Same as "show columns from employee;" 
    +----------+----------+------+-----+---------+-------+
    | Field    | Type     | Null | Key | Default | Extra |
     +----------+----------+------+-----+---------+-------+
    | Name | char(20) | YES | | NULL | | | Dept | char(20) | YES | | NULL |
     | | jobTitle | char(20) | YES  |     | NULL    |       | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.03 sec)   mysql>  show tables;
    +-------------------+
    | Tables_in_bedrock |
    +-------------------+
    | employee |
    +-------------------+

    mysql> INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
    mysql> INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
    mysql> INSERT into employee values ('Barney Rubble','Sales','Neighbor');
    mysql> INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
    Note: Data type used was CHAR. Other data types include:
    • CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters.
    • VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR.
    • INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295
    • FLOAT(M,N) : FLOAT(4,2) - Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large.
    • DATE, TEXT, BLOB, SET, ENUM
  4. Add a user. Use the MySQL SQL console to enter SQL commands. The command mysql with the correct login/password will connect you to the database. The admin tables are stored in the database "mysql".
    [prompt]$  mysql -h localhost -u root -ppassword
     Welcome to the MySQL monitor.  Commands end with ; or \g.
     Your MySQL connection id is 1 to server version: 3.23.41
     Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   mysql>  USE mysql;
    mysql> SHOW TABLES;
     +-----------------+
     | Tables_in_mysql |
     +-----------------+ 
     | columns_priv    |
     | db              |
     | func            |
     | host            |
     | tables_priv     |
     | user            |
     +-----------------+
        mysql>  INSERT INTO user (Host, User, Password, Select_priv) VALUES ('', 'Dude1', password('supersecret'), 'Y'); mysql>  FLUSH PRIVILEGES; -- Required each time one makes a change to the GRANT table mysql>  GRANT ALL PRIVILEGES ON bedrock.* TO Dude1; mysql>  FLUSH PRIVILEGES; -- Required each time one makes a change to the GRANT table mysql>  quit 
    Note:
    • There is NO space between the -p and the password! You can omit the password and you will be prompted for it.
    • The SQL flush command is equivalent to issuing the command:
      [prompt]$  mysqladmin reload
  5. Test the database:
    mysql>  SELECT * from employee;
    +-----------------+---------------+-------------+
     |      Name      |       Dept    |    jobTitle    | 
    +-----------------+---------------+-------------+ 
    | Fred Flinstone  | Quarry Worker | Rock Digger | 
    | Wilma Flinstone | Finance       |     Analyst     | 
    |  Barney Rubble  |  Sales        |    Neighbor    |
    | Betty Rubble    |   IT          |    Neighbor    | 
    +-----------------+---------------+-------------+ 
    1 row in set (0.00 sec)   mysql>  SELECT name FROM employee WHERE dept='Sales';
     +---------------+
     |      name     |
     +---------------+
     | Barney Rubble |
     +---------------+ 1 row in set (0.00 sec)                 
  6. Quit from the SQL shell:
    [prompt]$  quit
  7. Shutting down the database:
    [prompt]$  mysqladmin -u root -ppassword  shutdown - PREFERRED OR [prompt]$  /etc/rc.d/init.d/mysqld stop
    OR [prompt]$ service mysqld stop


rss feed widget