MYSQL

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h akt-vpc4.mansoor.com password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

user create

Here is an example:

Code:

1.   

2.  CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';

3.   

Database F1

Now if you check the mysql.user table you can find a new record in it. Notice that all priviliges are set to No so this user can do nothing in the DB. To add some preiviliges we can use the GRANT command as follows:

Code:

1.   

2.  GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';

3.   

Database F1

Here we have added only the most important priviliges to the user. With the setting above this user is good to run a CMS or a blog, however with such settings this user is not able to install them as it can not create tables. 

To add all priviliges to the user you don't have to list all of them but you can use the ALL shortcut as follows:

Code:

1.   

2.  GRANT ALL ON *.* TO 'user1'@'localhost';

3.   

Database F1

You can create a new MySQL user in one step as well using again the GRANT command with a small extension as here:

Code:

1.   

2.  GRANT ALL ON *.* TO 'user2'@'localhost' IDENTIFIED BY 'pass1';

3.   

Database F1

The above examples used dedicated commands, but sumtimes you maybe want to add a new MySQL user via directly editing the mysql.user table. In this case you just inserts a new record into the table with a normal INSERT command:

Code:

1.   

2.  INSERT INTO user (Host,User,Password)

3.     VALUES('localhost','user3',PASSWORD('pass3'));

4.   

Database F1

Or you can add some priviliges as well in a form like this:

Code:

1.   

2.  INSERT INTO user (Host,User,Password,Select_priv,Insert_priv)

3.     VALUES('localhost','user4',PASSWORD('pass3'),'Y','Y');

4.   

               User delete:

DROP USER userName;

                               User password chage:

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Logical Operations

MySQL includes full support of all basic logical operations.

AND (&&)

---

mysql> SELECT * FROM test WHERE

mysql> (name = "Bugs Bunny") AND

mysql> (phone_number = 5554321);

---

Result:
All records containing the name "Bugs Bunny" AND the phone number '5554321' will be displayed to the screen.

OR ( || )

---

mysql> SELECT * FROM test WHERE

mysql> (name = "Bugs Bunny") OR

mysql> (phone_number = 5554321);

---

Result:
All records containing the name "Bugs Bunny" OR the phone number '5554321' will be displayed to the screen.

NOT ( ! )

---

mysql> SELECT * FROM test WHERE

mysql> (name != "Bugs Bunny");

---

Result:
All records NOT containing the name "Bugs Bunny" will be displayed to the screen.

Order By

---

mysql> SELECT * FROM test WHERE

mysql> (name = "Bugs Bunny") ORDER BY

mysql> phone_number;

---

Result:
All records containing the name "Bugs Bunny" will be displayed to the screen, ordered in respect to the phone_number.

Search functions

MySQL offers the user the ability to perform both general and specific searches on data.

---

mysql> SELECT * FROM test WHERE

mysql> (name LIKE "%gs Bunny");

---

Result:
All records containing the partial string "gs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunny", "ags Bunny", "gs Bunny", and "234rtgs Bunny".

Notice that "LIKE" has been used instead of the equals sign (=). "LIKE" signifies that one is searching for an estimate of the data requested, and not necessarily an exact copy.

The '%' sign could be placed anywhere within the string. The method in which the server searches for a string is dependent upon where one places the '%' sign.

---

mysql> SELECT * FROM test WHERE

mysql> (name LIKE "Bugs Bunny%");

---

Result:
All records containing the partial string "Bugs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunnys", "Bugs Bunnyyyy453", "Bugs Bunnytrtrtrtrtr", but not "gs Bunny".

Focused Search Results

One can also perform searches and display only certain columns.

---

mysql> SELECT name FROM test WHERE

mysql> (name = "Bugs Bunny");

---

Result:

name

Bugs Bunny

Alter table

Another very important function of MySQL is the ability to modify previously created tables. This is accomplished via the ALTER statement. This function allows one to add, modify, and delete columns, as well as rename the table, among other functions.

Example: Rename the table

---

mysql> ALTER table test RENAME mytest;

---

Example: Add a column

---

mysql> ALTER table mytest ADD birthday DATE;

---

Example: Modify a column

---

mysql> ALTER table mytest CHANGE

mysql> name newname VARCHAR (25);

---

Example: Delete a column

---

mysql> ALTER table mytest DROP newname;

---

Executing the above four functions would modify test, creating the following table:

---

mysql> TABLE mytest (

> email VARCHAR (25),

> phone_number INT,

> ID INT AUTO_INCREMENT,

> birthday DATE );

---

Load data into table from file Example

cat   employee.txt
1         John Deo       Four        75
2         Max Ruin      Three       85
3         Arnold           Three      55
4         Krish Star     Four        60
5         John Mike     Four         60
6         Alex John      Four         55


mysql>load data local infile '/tmp/employee.txt' into table table_name;

Mysqlimport Command Example

cat   employee.txt
1         John Deo       Four        75
2         Max Ruin      Three       85
3         Arnold           Three      55
4         Krish Star     Four        60
5         John Mike     Four         60
6         Alex John      Four         55

 #mysqlimport -u root -ptmppassword --local database_name employee.txt

Here:
database_name = is the destination database name
employee.txt = the file name employee.txt here employee is table name of destination database

-->

Monitor MySQL restore progress with pv

#yum install pv

Run the restore command in Terminal 1

# mysql my_database < database_backup.sql

Watch the restore progress status on Terminal 2 with the below command

# pv database_backup.sql | mysql my_database

Output:

96.8MB 0:00:17 [5.51MB/s] [==>                                ] 11% ETA 0:02:10

To know the Databases/size/tables all throguh query

mysql> select sum(data_length+index_length)/1024/1024 AS total_mb,   sum(data_length)/1024/1024 as data_mb,
sum(index_length)/1024/1024 as index_db,
count(distinct table_schema) as sechema_cnt,
count(*) as tables,
curdate() as today,
version() from information_schema.tables\G;

Database backup

Step 1 # Backup MySQL Database

 Taking the full backup of all the tables including the data.
Use the following command to accomplish this:

mysqldump -u root -p databasename > databasename.sql

Restoring MySQL Database

Restoring the MySQL is very easy job. You can use the following to command to restore the accounts database from accounts.sql backup file.

mysql - u root -p databasename <  databasename.sql

                                    Table backup

Step 2 # Backup MySQL database specific Table

mysqldump -u root -p databasename tablename > databasename_tablename.sql

Restoring MySQL Database specific table.

Mysql –u root –p databasename < databasename_tablename.sql

                                    Table structure only backup

 Taking the backup of table structures only.
Use the following command to accomplish this:
mysqldump -u admin -p admin --no-data accounts > accounts.sql

                                    Table data only backup

c) Taking the backup data only.
Use the following command to accomplish this:
mysqldump -u admin -p admin --no-create-info accounts > accounts.sql

To Allow Remote access for linux mysql server

Server Linux:(192.168.176.216)

Step 1# vim /etc/my.cnf

#Add below line:

bind-address = 192.168.176.216 (linux mysql server ip)

#comment the below line if present

#skip-networking (should be disabled)

Do the below in linux mysql server

GRANT <PERMISSIONS> ON <Database>.* TO <User>@'<IP>' [IDENTIFIED BY '<Password>'][WITH GRANT OPTION];

GRANT <PERMISSIONS> ON <Database>.* TO <User>@'%' [IDENTIFIED BY '<Password>'][WITH GRANT OPTION];

[] -> optional

<> -> replace with appropriate info

PERMISSIONS -> Either ALL or any of SELECT, INSERT, UPDATE, DELETE, CREATE, DROP.

<IP> -> Remote IP you want to connect to the DB from.

<User> -> Username you want to connect to the DB with.

<Password> -> Password associated with the Username.

<Database> -> DB you want to connect to. This can be *.* instead of <Database>.* if you want to be

able to connect to all databases remotely with the specified user.

1.0         Setting up Windows to access the MySQL Server remotely

2.0              To Install the driver

Download the MySQL ODBC driver from the MySQL site (currently the released connector is ODBC 3.51, so that is what this document is based off of).

3.0              To Set up the data sources, etc.

1. Open Control Panel -> Administrative Tools -> Data Sources(ODBC)
2. Under the Drivers tab, there should be an entry with MySQL ODBC 3.51 Driver if the driver was installed properly.
3. Go to the System DSN tab.
4. Add a new System Data Source and select the MySQL ODBC driver.
5. Fill in the information on the Login Tab:

Data Source Name: The local name you want for your database Description: Just a description/comment Server Name/IP: address of the Linux server User: username to use to access the database Password: password to identify the user Database: The name of the Database on the server

6. Click the Test button to verify your settings.