Search This Blog

Thursday, February 26, 2015

cmake configure_file not replacing variables

most probably you may have

configure_file( in
                        out                       
                       @only )

here @only tells the cmake to only replace the variable defined like @variable@ and not to replace ${variable}

How To Set Up MySQL Master-Master Replication


MySQL replication is the process by which a single data set, stored in a MySQL database, will be live-copied to a second server. This configuration, called "master-slave" replication, is a typical setup. Our setup will be better than that, because master-master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform mysql read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data.

The examples in this article will be based on two VPS, named Server C and Server D.

Server C: 3.3.3.3
Server D: 4.4.4.4

Step 1 - Install and Configure MySQL on Server C

The first thing we need to do is to install the mysql-server and mysql-client packages on our server. We can do that by typing the following:
sudo apt-get install mysql-server mysql-client
By default, the mysql process will only accept connections on localhost (127.0.0.1). To change this default behavior and change a few other settings necessary for replication to work properly, we need to edit /etc/mysql/my.cnf on Server C. There are four lines that we need to change, which are currently set to the following:
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1
The first of those lines is to uniquely identify our particular server, in our replication configuration. We need to uncomment that line, by removing the "#" before it. The second line indicates the file in which changes to any mysql database or table will be logged.
The third line indicates which databases we want to replicate between our servers. You can add as many databases to this line as you'd like. The article will use a single database named "example" for the purposes of simplicity. And the last line tells our server to accept connections from the internet (by not listening on 127.0.0.1).
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = example
# bind-address            = 127.0.0.1
Now we need to restart mysql:
sudo service mysql restart
We next need to change some command-line settings within our mysql instance. Back at our shell, we can get to our root mysql user by typing the following:
mysql -u root -p 
Please note that the password this command will prompt you for is that of the root mysql user, not the root user on our droplet. To confirm that you are logged in to the mysql shell, the prompt should look like the following.
mysql> 
Once we are logged in, we need to run a few commands.
We need to create a pseudo-user that will be used for replicating data between our two VPS. The examples in this article will assume that you name this user "replicator". Replace "password" with the password you wish to use for replication.
create user 'replicator'@'%' identified by 'password'; 
Next, we need to give this user permissions to replicate our mysql data:
grant replication slave on *.* to 'replicator'@'%'; 
Permissions for replication cannot, unfortunately, be given on a per-database basis. Our user will only replicate the database(s) that we instruct it to in our config file.
For the final step of the initial Server C configuration, we need to get some information about the current MySQL instance which we will later provide to Server D.
The following command will output a few pieces of important information, which we will need to make note of:
show master status; 
The output will looking similiar to the following, and will have two pieces of critical information:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
We need to make a note of the file and position which will be used in the next step.

Step 2 - Install and Configure MySQL on Server D

We need to repeat the same steps that we followed on Server C. First we need to install it, which we can do with the following command:
sudo apt-get install mysql-server mysql-client
Once the two packages are properly installed, we need to configure it in much the same way as we configured Server C. We will start by editing the /etc/mysql/my.cnf file.
sudo nano /etc/mysql/my.cnf 
We need to change the same four lines in the configuration file as we changed earlier.
The defaults are listed below, followed by the changes we need to make.
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1
We need to change these four lines to match the lines below. Please note, that unlike Server C, the server-id for Server D cannot be set to 1.
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = example
# bind-address            = 127.0.0.1
After you save and quit that file, you need to restart mysql:
sudo service mysql restart
It is time to go into the mysql shell and set some more configuration options.
mysql -u root -p 
First, just as on Server C, we are going to create the pseudo-user which will be responsible for the replication. Replace "password" with the password you wish to use.
create user 'replicator'@'%' identified by 'password'; 
Next, we need to create the database that we are going to replicate across our VPS.
create database example; 
And we need to give our newly created 'replication' user permissions to replicate it.
grant replication slave on *.* to 'replicator'@'%'; 
The next step involves taking the information that we took a note of earlier and applying it to our mysql instance. This will allow replication to begin. The following should be typed at the mysql shell:
slave stop; 
CHANGE MASTER TO MASTER_HOST = '3.3.3.3', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107; 
slave start; 
You need to replace 'password' with the password that you have chosen for replication. Your values for MASTER_LOG_FILE and MASTER_LOG_POS may differ than those above. You should copy the values that "SHOW MASTER STATUS" returns on Server C.
The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position to use to replicate in the other direction (from Server D to Server C).
We can do that by typing the following:
SHOW MASTER STATUS; 
The output will look similiar to the following:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Take note of the file and position, as we will have to enter those on server C, to complete the two-way replication.
The next step will explain how to do that.

Step 3 - Completing Replication on Server C

Back on Server C, we need to finish configuring replication on the command line. Running this command will replicate all data from Server D.
 
slave stop; 
CHANGE MASTER TO MASTER_HOST = '4.4.4.4', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 107; 
slave start; 
Keep in mind that your values may differ from those above. Please also replace the value of MASTER_PASSWORD with the password you created when setting up the replication user.
The output will look similiar to the following:
Query OK, 0 rows affected (0.01 sec)
The last thing to do is to test that replication is working on both VPS. The last step will explain an easy way to test this configuration.

Step 4 - Testing Master-Master Replication

Now that have all the configuration set up, we are going to test it now. To do this, we are going to create a table in our example database on Server C and check on Server D to see if it shows up. Then, we are going to delete it from Server D and make sure it's no longer showing up on Server C.
We now need to create the database that will be replicated between the servers. We can do that by typing the following at the mysql shell:
create database example; 
Once that's done, let's create a dummy table on Server C:
create table example.dummy (`id` varchar(10)); 
We now are going to check Server D to see if our table exists.
 
show tables in example; 
We should see output similiar to the following:
+-------------------+
| Tables_in_example |
+-------------------+
| dummy             |
+-------------------+
1 row in set (0.00 sec)
The last test to do is to delete our dummy table from Server D. It should also be deleted from Server C.
We can do this by entering the following on Server D:
DROP TABLE dummy; 
To confirm this, running the "show tables" command on Server C will show no tables:
Empty set (0.00 sec)
And there you have it! Working mysql master-master replication.

Ref: https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

Test remote SQL connectivity without SQL tools

Have you ever been troubleshooting a problem with remote SQL Iproxy MP, remote DB, etc) and wanted to test to see if the local system account (or any account for that matter) could make a remote connection to SQL but you didn’t want to install the SQL tools just to make that test?  Seems there is a file type – UDL file – that you can simply create that will bring up a window to allow testing of remote connections to SQL.

Just go anywhere on your system and create an empty text file named anything but instead of txt make sure the extension is UDL.  Then, double-click on the file and up pops a SQL connectivity window to allow testing of remote SQL connections.

clip_image002
Very easy to use to test connectivity with a known user account or as local system using PSExec.

Thursday, February 19, 2015

ssh-copy-id in Mac OS X

For some reason Macs don’t have this useful script so this will add it.

sudo curl "http://phildawson.co.uk/ssh-copy-id" -o /usr/bin/ssh-copy-id
sudo chmod +x /usr/bin/ssh-copy-id

fix: /usr/bin/ssh-copy-id: ERROR: No identities found

Issue:

patelm@patelm-l ~
$ ssh-copy-id  patelm@10.22.56.146
/usr/bin/ssh-copy-id: ERROR: No identities found

the main issue is that the identity is no added

Check:

patelm@patelm-l ~
$ ssh-add.exe
Could not open a connection to your authentication agent.


Solution:

patelm@patelm-l ~
$ eval `ssh-agent`
Agent pid 8572

patelm@patelm-l ~
$ ssh-add.exe
Identity added: /home/patelm/.ssh/id_rsa (/home/patelm/.ssh/id_rsa)


Now you shall be able to


patelm@patelm-l ~
$ ssh-copy-id  patelm@10.22.56.146
Password:
Now try logging into the machine, with "ssh 'patelm@10.22.56.146'", and check in:
  ~/.ssh/authorized_keys
to make sure we haven't added extra keys that you weren't expecting.

your password less login shall work !

Monday, February 16, 2015

How to use MySQL from SSH (Linux shell)

Using MySQL from SSH may seem to be quite tricky if you’ve never done it before – but fear not – below is a list of MySQL commands that you can use to perform the required actions.
Start by logging-in using a SSH client, like PuTTY, to access the shell command line.
Below when you see # it means from the unix shell. And when you see mysql> it means from a MySQL prompt after logging into MySQL.
To login (from unix shell) use -h hostname only if needed
# mysql -h hostname -u root -p
This would ask you for a password and after providing the correct password you’d be logged-in to the MySQL prompt.
If you get an error with the above command then type # which mysql to make sure that mysql is installed properly and to find the directory. It would print out the mysql directory, and then you can use the above command as this: # [mysql dir]/bin/mysql -h hostname -u root -p
Create a database
mysql> create database [database name];
List all databases
mysql> show databases;
Switch to a database
mysql> use [database name];
To see all the tables in the selected database
mysql> show tables;
To see the database’s field formats
mysql> describe [table name];
To delete a database
mysql> drop database [database name];
To delete a table
mysql> drop table [table name];
Show all data in a table
mysql> SELECT * FROM [table name];
Show the columns and column information pertaining to the designated table
mysql> show columns from [table name];
Show certain selected rows with the value “whatever”
mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name “Bob” AND the phone number ’3444444′
mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name “Bob” AND the phone number ’3444444′ order by the phone_number field
mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters ‘bob’ AND the phone number ’3444444′
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
Show all records starting with the letters ‘bob’ AND the phone number ’3444444′ limit to records 1 through 5
mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a
mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";
Show unique records
mysql> SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc)
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Return number of rows
mysql> SELECT COUNT(*) FROM [table name];
Sum column
mysql> SELECT SUM(*) FROM [table name];
Join tables on common columns
mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating a new user (login as root, switch to the MySQL db, make the user, update privs)
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a user’s password from unix shell
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'
Change a user’s password from MySQL prompt (login as root, set the password, update privs)
# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password (stop the MySQL server process, start again with no grant tables, login to MySQL as root, set new password, exit MySQL and restart MySQL server)
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password
# mysqladmin -u root password newpassword
Update a root password
# mysqladmin -u root -p oldpassword newpassword
Allow the user “bob” to connect to the server from localhost using the password “passwd” (login as root, switch to the MySQL db, give privs, update privs)
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give a user privileges for a database (login as root, switch to the MySQL db, grant privs, update privs)
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

To update info already in a table
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
Delete a row(s) from a table
mysql> DELETE from [table name] where [field name] = 'whatever';
Update database permissions/privileges
mysql> flush privileges;
Delete a column
mysql> alter table [table name] drop column [column name];
Add a new column to db
mysql> alter table [table name] add column [new column name] varchar (20);
Change column name
mysql> alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes
mysql> alter table [table name] add unique ([column name]);
Make a column bigger
mysql> alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table
mysql> alter table [table name] drop index [colmn name];
Load a CSV file into a table
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Dump all databases for backup. Backup file is sql commands to recreate all db’s
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup
# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database
# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1
mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create Table Example 2
mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');