Monthly Archives: October 2013

mysql replication hints

Leave a reply

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should also specify the innodb_flush_log_at_trx_commit=1, sync_binlog=1 options

CREATE USER ‘replication_user’@’%’ IDENTIFIED BY ‘password’;
GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@’%’;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

It should be interesting for you file and position fields.

UNLOCK TABLES;

mysqldump –all-databases –master-data > /var/lib/mysql/master.db

use rsync to move you database to slave.

On MySQL slave you should do this:
mysql -p < ./master.db

CHANGE MASTER TO
MASTER_HOST=’master.mydomain.com’,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=”file”,
MASTER_LOG_POS=”position”;

SHOW SLAVE STATUS\G

config should look kike this:

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

Testing replication:
create database testdb;
create table testdb.simples (id int not null primary key) engine=ndb;
insert into testdb.simples values (999),(1),(2),(3);
select * from testdb.simples;

mysql enable remote access

Leave a reply

Edit /etc/my.cnf and locate line that reads as follows
[mysqld]

Make sure line skip-networking is commented (or remove line) and add the following line:
bind-address=[YOUR.SERVER.IP.ADDRESS]

For example, if your MySQL server IP is 10.10.10.1 the config should look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 10.10.10.1
# skip-networking

Also make sure you have access from outside for that user:
GRANT USAGE ON *.* TO '[User1]'@'%' IDENTIFIED BY '[password]';

mysql reset root password

Leave a reply

Stop mysql services:

/etc/init.d/mysql stop

Start mysql in safe mode and skip user authentication:
mysqld_safe --skip-grant-tables &

Login to mysql as root, but you need use other console for this:
mysql -u root

Set a new password:
UPDATE mysql.user SET Password=PASSWORD('new_pass') WHERE User='root';

When you modify the grant tables manually you need to run:
flush privileges;

Restart the mysql services:
/etc/init.d/mysql start

mysql user management

Leave a reply

Show users
SELECT user, host, password FROM mysql.user;

Create new user accessible only on localhost
CREATE USER '[User1]'@'localhost' IDENTIFIED BY '[password]';

Create new user accessible from anywhere
CREATE USER '[User1]'@'%' IDENTIFIED BY '[password]';

Grant permissions to all databases for User1 from localhost only
GRANT ALL PRIVILEGES ON *.* TO 'User1'@'localhost' WITH GRANT OPTION;

Grant permissions to specified database for User1 from anywhere
GRANT ALL PRIVILEGES ON [db_name].* TO 'User1'@'%' WITH GRANT OPTION;

Create a new user and grant permissions to database at once
GRANT ALL PRIVILEGES ON [db_name].* To 'user'@'hostname' IDENTIFIED BY '[password]';

Show users permissions
SHOW GRANTS FOR [username]@localhost;

mysql fast hints

Leave a reply

Show MySQL uptime:
mysqladmin version

Login to MySQL
mysql -h [hostname] -u [username] -p

Show databases
show databases;

Create database
create database [name];

Delete database
drop database [name];

Use database
use [database];

Show tables
show tables;

Show columns
show columns from [table];

Find and replace string
update [table_name] set [column_name] = replace([column_name],'[string_to_find]','[string_to_replace]');

To backup and restore a mysql database see one of my earlier posts here:
http://www.homecomputerlab.com/backing-up-mysql-databases

 

mysql usage hints

Leave a reply

Working with MySQL Database

Create a database with a specified name if it does not exist in database server

1
CREATE DATABASE [IF NOT EXISTS] database_name

Use database or change current database to another database you are working with

1
USE database_name

Drop a database with specified name permanently. All physical file associated with the database is no longer exists.

1
DROP DATABASE [IF EXISTS] database_name

Show all available databases in the MySQL database server

1
SHOW DATABASES

 

Working with Table

Lists all tables in a database.

1
SHOW TABLES

Create table statement that defines the structure of table .

1
2
3
4
5
6
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name(
   key type(size) NOT NULL PRIMARY KEY AUTO_INCREMENT,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   …
) Engine= [InnoDB|MyISAM]|…]

Altering table structure

Actions can be one of the following actions:

1
ADD [COLUMN]

Add a new column into a table

1
DROP [COLUMN]

Drop an existing column in a table

1
ADD INDEX [name](column_name, …)

Add index with a specific name to a table on a column

1
DROP INDEX index_name Drop an index from a table

Add primary key into a tables

1
ADD PRIMARY KEY (column_name,…)

Remove primary key from a table

1
DROP PRIMARY KEY

Deleting table permanently

1
2
DROP TABLE [IF EXISTS] table_name [, name2, …]
[RESTRICT | CASCADE]

Get information about the table or column.

1
2
DESCRIBE table [column_name]
DESC table [column_name

 

Working with Index

Creating an index with the specified name on a table

1
2
CREATE [UNIQUE|FULLTEXT] INDEX index_name
ON table (column_name,…)

Removing a specified index from table

1
DROP INDEX index_name

 

Querying Data

Query complete data in a database table

1
SELECT * FROM table_name

Query specified data which is shown in the column list from a database table

1
2
SELECT column_name, column_name2….
FROM table_name

Query unique records

1
2
SELECT DISTINCT (column_name)
FROM table_name

Query data from multiples table using join

1
2
3
SELECT *
FROM table_name1
INNER JOIN table_name2 ON conditions

 

1
2
3
SELECT *
FROM table_name1
LEFT JOIN table_name2 ON conditions

 

1
2
SELECT *
FROM table_name1

Counting number of rows in a database table

1
2
SELECT COUNT (*)
FROM table_name

Sorting ascending or descending based on one or more columns:

1
2
3
SELECT column_name, column_name2….
FROM table_name
ORDER BY column_name ASC [DESC], column_name2 ASC [DESC],…

Group the retrieved rows data:

1
2
3
SELECT *
FROM table_name
GROUP BY column_name

 

Matching Data based on a pattern

Matching data using LIKE operator:

1
2
SELECT * FROM table_name
WHERE column_name LIKE ‘%value%’

 

Matching data using regular expression

 

1
2
SELECT * FROM table_name
WHERE column_name RLIKE ‘regular_expression’

SSH based filesystem

Leave a reply

This is a file system client based on the SSH File Transfer Protocol.

  • Based on FUSE (the best userspace file system framework for linux 😉
  • Multi-threading: more than one request can be on it’s way to the server
  • Allowing large reads (max 64k)
  • Caching directory contents
yum install fuse-sshfs

If you want use sshfs as non root user: usermod -a -G fuse vitalijus

Mounting SSHFS

sshfs user@remote:/path local_mount_point options

You can even use fstab to use SSHFS permanently:
user@dremote:/path local_mount_point fuse.sshfs defaults,allow_other,_netdev 0 0

rpm cheatsheet – rpm usage

Leave a reply

rpm -ivh {rpm-file}                            Install the package
rpm -Uvh {rpm-file}                          Upgrade package
rpm -ev {package}                           Erase/remove/ an installed package
rpm -ev –nodeps {package}            Erase/remove/ an installed package
without checking for dependencies
rpm -qa                                             Display all installed packages
rpm -qi {package}                            Display installed information
rpm -qf {/path/to/file}                      What package a file belongs to
rpm -qc {pacakge-name}                 Display list of configuration file(s)
rpm -qa –last                                   Display all recently installed RPMs
rpm -qpR {.rpm-file}
rpm -qR {package}                           Find dependencies