CS & IT Solutions

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.

To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile ‘uniq.csv’ into table tblUniq
fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(uniqName, uniqCity, uniqComments)

The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

courtesy http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/

A frequent error message received when using the mysql command line utility is: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ While this error message can be frustrating, the solution is simple.

When connecting to a MySQL server located on the local system, the mysql client connects thorugh a local file called a socket instead of connecting to the localhost loopback address 127.0.0.1. For the mysql client, the default location of this socket file is /tmp/mysql.sock. However, for a variety of reasons, many MySQL installations place this socket file somewhere else like /var/lib/mysql/mysql.sock.

While it is possible to make this work by specifying the socket file directly in the mysql client command

mysql –socket=/var/lib/mysql/mysql.sock …

it is painful to type this in every time. If you must do so this way (because you don’t have permissions to the file in the solution below), you could create an alias in your shell to make this work (like alias mysql=”mysql –socket=/var/lib/mysql/mysql.sock” depending on your shell).

To make your life easier, you can make a simple change to the MySQL configuration file /etc/my.cnf that will permanently set the socket file used by the mysql client. After making a backup copy of /etc/my.cnf, open it in your favorite editor. The file is divided into sections such as

[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/usr/local/mysql

If there is not currently a section called [client], add one at the bottom of the file and copy the socket= line under the [mysqld] section such as:

[client]
socket=/var/lib/mysql/mysql.sock

If there is already a [client] section in the my.cnf file, add or edit the socket line as appropriate. You won’t need to restart your server or any other processes. Subsequent uses of the mysql client will use the proper socket file.

courtesy http://www.tech-recipes.com/rx/762/solve-cant-connect-to-local-mysql
-server-through-socket-tmpmysqlsock/

MySQL tables are easy to extend with additional columns.

To add a column called email to the contacts table created in Create a basic MySQL table with a datatype of VARCHAR(80), use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

courtesy http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table/

all access to a system through a single account with all abilities is typically dangerous. Creating MySQL user accounts allows privileges to be granted as appropriate.

To create a user jsmith with password Secret15 and allow them to do anything with the database named accounts, connect to the database with mysql and issue the command:

grant all on accounts.* to jsmith@localhost identified by ‘Secret15′;

courtesy http://www.tech-recipes.com/rx/191/create_a_mysql_user_account/

Change the root user password for MySQL using mysqladmin

To change the MySQL root password to PaSsWoRd, use:

mysqladmin -u root password PaSsWoRd

courtesy http://www.tech-recipes.com/rx/19/change-the-mysql-root-user-password/