There are several ways to start and stop mysql. Let me share the things that I know.

Starting the mysql server:

           Server~Name ] # mysqld_safe –-defaults-file =  /path/to/my.cnf -–datadir = path/to/data/directory  –basedir = /path/to/base/directory –user = mysql &

Note : mysqld and mysqld_safe are not the same.

“mysqld” is the MySQL server daemon program also known as MySQL Server. While mysqld_safe adds some safety features to mysqld. It will restart the server when an error occurs and logging runtime information to an error log file. 

Stopping the mysql server:

        Server~Name ] # mysqladmin -h<IP> -u<User> -p<Password> -P<Port> shutdown

* There is a simplest way to start and stop mysql. Follow the simple steps to achieve that.

Step 1: Copy the mysql.server start up script to /etc/init.d/

        Server~Name ]# cp /mysql/base/directory/support-files/mysql.server   /etc/init.d/mysql

Note : /etc/init.d contains the various start/stop script of the system. This will start up the scripts while the system starts up.

Step 2 : Edit the /etc/init.d/mysql and provide the mysql base-directory and data-directory path

          Server~Name ]# vi /etc/init.d/mysql

               base-dir=/path/to/base/directory/

               path-dir=/path/to/data/directory/

Step 3 : Save the changes done using :wq

Now the mysql can be start and stop using the simple script as follows.

       Server~Name ] # /etc/init.d/mysql  { start|stop|restart}

First thing that you need to do is to check whether port 3306 in the remote host is open.You can use telnet command to check it.

localhost$] telnet 10.168.1.2 3306

Trying 10.168.1.2…

Connected to remote6.106 (10.168.1.2).

Escape character is ‘^]’.

8

5.1.36-logJU[G1_k{BI*3`m"]0w’Z

If the port is open you would get something like above

Now login to the remote host using ssh client.

remotehost $] ssh 10.168.1.2

Then login to mysql.

remotehost $] mysql -u root -p

password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 25630

Server version: 5.1.36-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

If you want a new user to be created that you would use to login from your local server, you can create that user and then give the necessary grants as shown below.

mysql>grant all privileges on *.* to boss@’10.168.1.1′;

Query OK, 0 rows affected (0.01 sec

Now that you have created the user for remote login you can login from your local machine.

Take the below command as an example to login to remote MySQL.

localhost$] mysql -uboss -h10.168.1.2 -p

password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 25630

Server version: 5.1.36-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

That’s it. You are now connected to the remote MySQL server.

There are two ways to reset MySQL root password as follows:

FIRST WAY

• Stop the server
• Open a text file, give the below statement and save the file (for example the filename is /tmp/newpass.txt).
  • UPDATE mysql.user SET Password=PASSWORD(’YourNewPassword’) WHERE User=’root’ and host=’localhost’;
  • FLUSH PRIVILEGES;
• Now, start the server with –init-file option
  • mysqld_safe --init-file=/tmp/newpass.txt &
• You can login in to mysql with your new root password now.
• Once you have logged in successfully, remember to delete the text file (/tmp/newpass.txt).

SECOND WAY

• Stop the server
• Start the server with –skip-grant-tables option
  • mysqld_safe --skip-grant-tables &

• Connect the server using mysql client utility (eg: mysql). When it asks for password, just hit enter.
• Issue the below statements.
  • UPDATE mysql.user SET Password=PASSWORD(’ YourNewPassword ‘) WHERE User=’root’ and host=’localhost’;
  • FLUSH PRIVILEGES;
• You can login in to mysql with your new root password now.

Note:  Second way of resetting password is not secure

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysql.sock’

1. Check whether mysqld server is running or not, using the below command

ps -ef | grep mysql

If mysqld is running, then check the socket path in the .cnf file and give the correct path when connecting.

2. Check the file permission of that socket file. If the file doesn’t have mysql permission, just change permission to mysql using the below command.

chown mysql:mysql sockfile_path

3. If the socket file is missing then you have to restart your mysqld server.

mysql> explain SELECT users.user_id FROM friends INNER JOIN users ON friends.friend_to = users.user_id WHERE friends.friend_from=108643 and friends.friend_to=108643;
+—-+————-+——-+——+—————+——+———+——+——+—————————————————–+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+—-+————-+——-+——+—————+——+———+——+——+—————————————————–+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+—-+————-+——-+——+—————+——+———+——+——+—————————————————–+
1 row in set (0.00 sec)

This means that there is no matching rows for the condition that was provided.

/usr/local/src/mysql-5.1.42-linux-x86_64-glibc23/bin/mysqld_safe  –datadir=/usr/local/src/mysql-5.1.42-linux-x86_64-glibc23/data –basedir=/usr/local/mysql-5.1.42-linux-x86_64-glibc23 &

I got the error shown below

mysqld_safe The file /usr/local/mysql/bin/mysqld does not exist or is not executable.

and I fixed by entering in to MySQL installed directory and started the server

cd /usr/local/src/mysql-5.1.42-linux-x86_64-glibc23

./bin/mysqld_safe  –datadir=/usr/local/src/mysql-5.1.42-linux-x86_64-glibc23/data –basedir=/usr/local/mysql-5.1.42-linux-x86_64-glibc23 &

MySQL Cluster executes the queries depending on the below type of scan’s it does

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let’s say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you’ve controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here’s how each ones works:

1. Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
2. Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
3. Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
4. Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.

Thanks,

Suresh Kuna

When we take a mysqldump with routines or show procedure status where Db=’dbname’;

The mysqld server got an error like “Lost connection to mysqld server”, when we login into server the will be running and it got restarted.

To fix this, upgrade the db by using mysql_upgrade and it got fixed for me.

Thanks,

Suresh Kuna

1) It indicates network connectivity trouble. If the error message includes “during query” then we have to look at the query and the number of rows being sent.
Then try increasing “net_read_timeout” variable from its default value.
2) It can happen when the client is attempting the initial connection to the server. If your “connect_timeout” value is set to only a few seconds, you can increase the same for resolving this issue.
3) It can occur when using BLOB values that are larger than “max_allowed_packet” and may get “packet too large” as error, then increase the “max_allowed_packet” value to resolve the same.

Suresh Kuna

081022 6:19:02 InnoDB: Warning: shutting down a not properly started
InnoDB: or created database!
081022 6:19:02 /usr/local/mysql4020/libexec/mysqld: Shutdown Complete

InnoDB: Error: data file /mysql/data/mysqld/ibdata1 is of a different size
InnoDB: 128000 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 65536 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
081022 6:19:04 Can’t init databases
081022 6:19:04 Aborting

Then change the below variable as the same in the master cnf file

innodb_data_file_path =ibdata1:2000M;ibdata2:100M:autoextend
and start the mysqld server.

Suresh Kuna